I am trying to get rid of some Outlook templates and want Excel to read my Sheet and create the email.
A compiler error generates on the Add Recipients line.
This worked with a meeting for some reason, it let me just refer to the cell.
But the olMailItem does not.
I don't want to hard code the Recipients in the macro (it says needs argument)
I want them to be read from one cell.
Any ideas appreciated.
Sub EmailTest()
If MsgBox("Create Test 'Yes' or 'No'", vbYesNo, "Test Email?") = vbNo Then Exit Sub
Worksheets("Outlook").Visible = True
Worksheets("Outlook").Activate
Dim olApp As Outlook.Application
Dim myItem As Outlook.MailItem
Dim r As Long
Set olApp = GetObject("", "Outlook.Application")
Set myItem = olApp.CreateItem(olMailtem)
' Start at row 2
r = 2
With myItem
.Subject = Cells(r, 1).Value
.Recipients.Add = Cells(r, 8).Value
.BodyFormat = olFormatHTML
.HTMLBody = Cells(r, 17).Value
.Importance = olImportanceHigh
.Display
End With
End Sub
A compiler error generates on the Add Recipients line.
This worked with a meeting for some reason, it let me just refer to the cell.
But the olMailItem does not.
I don't want to hard code the Recipients in the macro (it says needs argument)
I want them to be read from one cell.
Any ideas appreciated.
Sub EmailTest()
If MsgBox("Create Test 'Yes' or 'No'", vbYesNo, "Test Email?") = vbNo Then Exit Sub
Worksheets("Outlook").Visible = True
Worksheets("Outlook").Activate
Dim olApp As Outlook.Application
Dim myItem As Outlook.MailItem
Dim r As Long
Set olApp = GetObject("", "Outlook.Application")
Set myItem = olApp.CreateItem(olMailtem)
' Start at row 2
r = 2
With myItem
.Subject = Cells(r, 1).Value
.Recipients.Add = Cells(r, 8).Value
.BodyFormat = olFormatHTML
.HTMLBody = Cells(r, 17).Value
.Importance = olImportanceHigh
.Display
End With
End Sub
Last edited: