Macro sending outlook template from Excel list

[Daro346]

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.

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

 

[Diane Poremsky]

You need to move the set new message inside the for each loop.


Sent from my iPhone using Tapatalk

 

[Daro346]

You need to move the set new message inside the for each loop.


Sent from my iPhone using Tapatalk

Thanks.. I moved the "set myitem" after the "for each mycell". When the macro runs it creates 2 drafts for each line in excel... thoughts?

 

[Diane Poremsky]

this is creating two drafts? oh, you have an imap account - it's not uncommon for duplicates in drafts when you use imap. (I've always felt it was worse with gmail due to how they hande imap.)

For Each MyCell In MyRange
set myitem =....
With MyItem
.To = Cells(MyCell.Row, 1).Value
.Subject = Cells(MyCell.Row, 2).Value
.Save
End With

Next MyCell

 

[Daro346]

this is using Outlook. It sounds like there's no work around, correct?

 

[Diane Poremsky]

yeah, no workaound. it might be possible to move or save to another folder, but you might still get duplicates. i don't have any code samples to test.

 

[Daro346]

thanks... now that I have it working, my follow on question.
In my outlook template there are fields for Greeting, Meeting Date, Next Meeting Date and Time.
Is it possible to take these fields from the excel spreadsheet and populate the fields in the template? (i.e. like a mail merge of sorts)