I have an excel macro that creates an email in outlook using a template. Col A is the email address; Col B is the subject. The macro works, except when it loops through the list, instead of creating a new email and saving it as a draft, it overwrites the email.
Code:
Sub test_send()
Dim TemplName As String
Dim FolderName As String
Dim MeetingDate
Dim FirstNames As String
Dim LastName As String
Dim NextMeetingDate As String
Dim NextMeetingTime As String
Dim enviro As String
Dim OL As Object, MailSendItem As Object
Dim MyItem As Object
Dim MyCell As Range
Dim MyRange As Range
' create file location for template
enviro = CStr(Environ("USERPROFILE"))
FolderName = "\Box Sync\Templates\"
TemplName = "Meeting Summary.oft"
strtemplatename = enviro + FolderName + TemplName
Set OL = CreateObject("Outlook.Application")
Set MyItem = OL.CreateItemFromTemplate(strtemplatename)
' column a = email address
' colum b = email subject
Set MyRange = Range("A2:B14")
For Each MyCell In MyRange
With MyItem
.To = Cells(MyCell.Row, 1).Value
.Subject = Cells(MyCell.Row, 2).Value
.Save
End With
Next MyCell
Set OL = Nothing
End Sub
