send email from Excel using outlook template

Status
Not open for further replies.

Daro346

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

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

New Member
Outlook version
Outlook 2010 32 bit
Email Account
IMAP
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:
Code:
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
 
Status
Not open for further replies.
Thread starter Similar threads Forum Replies Date
M Extract all links from Outlook email, send to Excel Using Outlook 2
S Send email from excel based on a condition (outlook 2007) Outlook VBA and Custom Forms 1
D Outlook VBA to open Excel attachment and send recipient's email address to a workbook cell? Using Outlook 4
J email will not send from word, excel, adobe, until outlook is opened Using Outlook 1
T After I send a new email, it remains in the Draft folder Using Outlook.com accounts in Outlook 3
S Change VBA script to send HTML email instead of text Outlook VBA and Custom Forms 3
M ERROR: None of your email accounts could send to this recipient Using Outlook 2
J Add an Attachment Using an Array and Match first 17 Letters to Matching Template .oft to Send eMail Outlook VBA and Custom Forms 2
ChrisK2 Send email to advertise@slipstick.com fails: "The group advertising isn't set up to receive messages from..." Using Outlook 3
R auto send email when meeting closes from a shared calendar only Outlook VBA and Custom Forms 2
X Unable to send an email from one account to another on same PC Using Outlook 2
M VBA to send reminder email if no response Using Outlook 13
D Using a VBA Custom Form to Send Reoccurring Email Upon Task Completion Outlook VBA and Custom Forms 4
M Can't send email in outlook.com Using Outlook 9
B When working on emails in a certain folder, when I hit reply or reply all, I would like it re always reply all and add an email address to send to Outlook VBA and Custom Forms 3
A Outlook - Send New 20 Attachments through Email Using Outlook 4
S Send email via SMTP - use transport rules to add to senders inbox (then rule to move to sent items Exchange Server Administration 1
R Prompt asking the user to send email to folder as *.msg file Outlook VBA and Custom Forms 1
D Is it possible to automatically send an email when it is moved to a folder? Exchange Server Administration 1
Diane Poremsky How to Send Email When Traveling or Using Public Wi-Fi New Slipstick.com Articles 0
B VBA Help Email that will save as draft and send as attachment Outlook VBA and Custom Forms 3
A Automatically send email based on drop-down field? Outlook VBA and Custom Forms 2
H send reminder if no reply received on first or original email using macro Using Outlook 2
K Macro Not Executing then send email from Explorer Outlook VBA and Custom Forms 3
A Block user to send emails to specific set of email ids Using Outlook 1
T Can't send email through connected account (outlook.live.com) - goes to Drafts folder Using Outlook.com accounts in Outlook 3
C Custom Application Form send Email to Another User Using Outlook 1
C Macro to send email after changing from address and adding signature Outlook VBA and Custom Forms 1
M Send an Email When a Reminder Fires Outlook VBA and Custom Forms 1
Diane Poremsky Send an Email When You Add an Appointment to Your Calendar New Slipstick.com Articles 0
Diane Poremsky Send email to all addresses for one Contact New Slipstick.com Articles 0
G "Delay Delivery" Email fails to send if opened while in Outbox Using Outlook 4
E Send a Reminder/Task to certain Email Recipient Using Outlook 5
Diane Poremsky Send an Email When a Reminder Fires New Slipstick.com Articles 0
A Send email from BCM BCM (Business Contact Manager) 5
T Using a macro to send email to diffrent address Outlook VBA and Custom Forms 1
V Outlook 2010 – Send email based on categories Using Outlook 2
M Use a macro to send files by email Outlook VBA and Custom Forms 3
S Can't send messages using Outlook 2007 from a business email Using Outlook 3
C outlook 2013-how do i send email to myself? Using Outlook 1
K Struggling to send team email Using Outlook 1
H Problems With Outlook 2013 VBA To Send and Print an email Outlook VBA and Custom Forms 1
R Outlook 2010 Send from other email address uses account address instead Using Outlook 1
R Email Send & Receive Turned off but sends automatically anyway Using Outlook 1
A Application_ItemSend not accessed when sending email outside outlook (i.e. word or send to mail from Using Outlook 7
R Outlook 2013 cannot send email error 0x700700c1 Using Outlook 1
SirFishAlot Send atachment with mass email using BCM, and Publisher 2013 BCM (Business Contact Manager) 1
A In email, tab sequence and Shift-S for send Using Outlook 4
K can't send email to other domains Using Outlook 1
V Cannot send email to group Using Outlook 12
Similar threads


















































Top