Macro sending outlook template from Excel list

Daro346

New Member
Outlook version
Outlook 2010 32 bit
Email Account
IMAP
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
 

Daro346

New Member
Outlook version
Outlook 2010 32 bit
Email Account
IMAP
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

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
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

New Member
Outlook version
Outlook 2010 32 bit
Email Account
IMAP
this is using Outlook. It sounds like there's no work around, correct?
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
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

New Member
Outlook version
Outlook 2010 32 bit
Email Account
IMAP
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)
 
Top