Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

send email from Excel using outlook template

0

I am trying to use excel to create an outlook message based on a template.  I am not a programmer but have put together macros based on postings I've found online.  I'll post the current macro code.  It's only creating the entry on the last line from the excel sheet.I am trying to use excel to create an outlook message based on a template.  I am not a programmer but have put together macros based on postings I've found online.  I'll post the current macro code.  It's only creating the entry on the last line from the excel sheet.

also, from excel worksheet
Column A= email address
Column B= email subject

Sub Send_Email_to_List()
 
    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
    
    
' 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)
 
For Each xCell In ActiveSheet.Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    
    With MyItem
        .To = xCell.Value
        .Subject = Cells(xCell.Row, 3).Value
        .Save
    End With
 
Next xCell
 
Set OL = Nothing
  
[/CODE]End Sub
Answer
Discuss

Discussion

This looks like a different macro than the one to which you linked. Are you mainly trying to now just get data into the macro from the worksheet?
don (rep: 1989) Nov 1, '16 at 7:43 pm
I am trying to use excel to create an outlook message based on a template.  I am not a programmer but have put together macros based on postings I've found online.  I'll post the current macro code.  It's only creating the entry on the last line from the excel sheet.
daro346 Nov 3, '16 at 8:10 am
Sub Send_Email_to_List()
 
    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
    
    
' 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)
 
For Each xCell In ActiveSheet.Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    
    With MyItem
        .To = xCell.Value
        .Subject = Cells(xCell.Row, 3).Value
        .Save
    End With
 
Next xCell
 
Set OL = Nothing
 
End Sub
daro346 Nov 3, '16 at 8:12 am
also, from excel worksheet
Column A= email address
Column B= email subject
daro346 Nov 3, '16 at 8:25 am
Stop and read the rules at the top of any page in this forum! You need to edit your original questoin with this information so that it is easier to follow.
don (rep: 1989) Nov 3, '16 at 11:46 pm
Add to Discussion



Answer the Question

You must create an account to use the forum. Create an Account or Login