send email from Excel using outlook template

[Daro346]

I found the macro that takes fields from excel and sends an email.

I would like to create an email from an outlook template, populate fields from excel and save it as a draft.

Here's the outlook/VBA macro - I'd like to pull from excel the following columns:

Email Address, Meeting Date, First Names, Last Name, Next Meeting Date, Next Meeting Time and Next Meeting Location. I started to set up the macro to enter these items, however, it would be better if I could pull the info from an excel spreadsheet.

Is there a way to run the macro from outlook and pull data from an excel spreadsheet or is it better to start with a macro from excel? I don't want excel to send the email, only save the draft

Sub SummaryLetter()

Dim TemplName As String

Dim DriveLetter

Dim FolderName As String

Dim MeetingDate

Dim FirstNames As String

Dim LastName As String

Dim NextMeetingDate As String

Dim NextMeetingTime As String

DriveLetter = "C:\Users\"

UserName = Environ("Username")

FolderName = "\Box Sync\Templates\"

TemplName = "Meeting Summary.oft"

strTemplateName = DriveLetter + UserName + FolderName + TemplName

Set msg = Application.CreateItemFromTemplate(strTemplateName)

MeetingDate = InputBox("Meeting Date")

FirstNames = InputBox("First Names")

LastName = InputBox("Last Name")

NextMeetingDate = InputBox("Next Meeting Day, Date")

NextMeetingTime = InputBox("time")

msg.Display

End Sub

I'm not a programmer, an end user, trying to automate things to save time. Thanks~

 

[Diane Poremsky]

you can do it either way - macro in excel or macro in outlook. This article shows how to connect to excel - Use a Macro to Copy Data in an Email to Excel - instead of writing to cells, you'll read. This one - Create Appointments Using Spreadsheet Data - shows how to use excel data to create appointments. It would need just a little tweaking to create messages.

on your template path -
trTemplateName = DriveLetter + UserName + FolderName + TemplName
i would use this - but using userprofile, you don't need to use the drive path variable.
enviro = CStr(Environ("USERPROFILE"))
trTemplateName= enviro & FolderName + TemplName
(& or + do the same thing - you can use the one you prefer - i always use &)

you'll grab the field values using Cells(i, 9) - where i is the row and 9 is the column.
MeetingDate = InputBox("Meeting Date")
MeetingDate = Cells(i, 9)

 

[Daro346]

Thanks! I'll give it a try

 

[Daro346]

you can do it either way - macro in excel or macro in outlook. This article shows how to connect to excel - Use a Macro to Copy Data in an Email to Excel - instead of writing to cells, you'll read. This one - Create Appointments Using Spreadsheet Data - shows how to use excel data to create appointments. It would need just a little tweaking to create messages.

on your template path -
trTemplateName = DriveLetter + UserName + FolderName + TemplName
i would use this - but using userprofile, you don't need to use the drive path variable.
enviro = CStr(Environ("USERPROFILE"))
trTemplateName= enviro & FolderName + TemplName
(& or + do the same thing - you can use the one you prefer - i always use &)

you'll grab the field values using Cells(i, 9) - where i is the row and 9 is the column.
MeetingDate = InputBox("Meeting Date")
MeetingDate = Cells(i, 9)

Thanks... I've made some progress.... The macro code is below. It's only creating the entry on the last line from the excel sheet. I decided to start with Excel and run a macro to create an outlook message based on a template. column 1 is the email address, column 2 is the email subject. any help would be appreciated.

Macro:

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