I've been trying to create a sub that would take some information from an Excel selection and create a new task on Outlook. The body of the task should feature the comment from the first cell (which it already does) but before all that I want to paste the range as it looks in Excel, then the comment, and then again, the range.
Here's my code:
Sub CreateReminder()
Dim olApp As Object
Dim olRem As Object
Dim myRange As Range
Dim contact As String
Dim company As String
Dim city As String
Dim state As String
Dim cmt As comment
Dim comment As String
Dim strdate As Date
Dim remdate As Date
Set olApp = CreateObject("Outlook.Application")
Set olRem = olApp.CreateItem(3)
Set myRange = Selection
If ActiveCell.comment Is Nothing Then
Exit Sub
Else
Set cmt = ActiveCell.comment
End If
company = myRange.Columns(1).Text
contact = myRange.Columns(2).Text
If InStr(contact, "/") <> 0 Then
contact = Left(contact, InStr(contact, "/") - 1)
End If
city = myRange.Columns(7).Text
state = myRange.Columns(8).Text
myRange.Copy
comment = cmt.Text
strdate = Date
remdate = Format(Now)
rangeaddress = myRange.Address
wrksheetname = ActiveSheet.Name
With olRem
.Subject = "Call " & contact & " - " & company & " - " & city & ", " & state
.display
SendKeys "{TAB 9}"
SendKeys "^{v}"
.body = Chr(10) & comment & Chr(10)
'.startdate = strdate
'.remindertime = remdate
'.reminderset = True
'.showcategoriesdialog
End With
Set olApp = Nothing
Set olRem = Nothing
End Sub
As you can see, I am able to paste using a SendKeys method, but it is sort of a hack, and not... sophisticated. I'm sure there's another way of doing it, any ideas?
I found code for pasting as HTML to an email, but as I understand, the Mail item allows for HTML, but not the Task item.