[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


creating a macro to "write" an email

Posted on 2006-05-12
Medium Priority
Last Modified: 2010-10-26

I wanted to create a macro in Outlook 2003, but discovered it won't record macros, so I'm a little lost as to what the usual syntax is for Outlook macros (I do a lot of VB in Excel).  

How can I create a macro that will create a new email (not send it, just create and populate) and insert a Word document into the body before the signature and populate the subject line?

Even better, if I have a list of names and email addresses, have outlook create an email for each email address and use the associated names to replace keywords in the Word doc. In this case, I might just want Outlook to go ahead and send the message too.


Question by:ugeb
  • 3
  • 3

Expert Comment

ID: 16675270
to me the right approach for this seems to be to create a script (or excelmacro ) to do this for you , so not from within Outlook.

If you run a script that uses MAPI to send a mail - it is Outlook that is invoked in the background to create and send your mail.
So I wouldn't look for this functionality from within Outlook but in a script or vb-project.

Hope this makes sense....   :-)
LVL 11

Author Comment

ID: 16677700

Thank you for the response, but the point is I have no idea how to do that.  Regardless of where the macro or VB originates, I have no idea what the commands are or any of that.  That's the crux of my problem and the real question I have.

Can you give me anything that illustrates what you're describing?
LVL 76

Expert Comment

by:David Lee
ID: 16678807
Greetings, ugeb.

Here's an Outlook macro for creating a new message from code.  It includes setting the subject line and attaching a file.  What you've described in your third paragraph is a mail merge.  There are instructions in both Word and Outlook's online help for doing that.

Sub NewOutlookMsg()
    Dim olkMessage As Outlook.MailItem
    Set olkMessage = Application.CreateItem(olMailItem)
    With olkMessage
        'Change the subject line text on the next line as desired
        .Subject = "My Subject"
        'Change the file name and path of the file to be attached
        .Attachments.Add "C:\MyFile.Doc"
    End With
End Sub

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

LVL 11

Author Comment

ID: 16679085
Thank you, this definitely gets me a step closer.

What about inserting a document into the body instead of attaching it?  
Can I replace the text "XXXXX" with a name or something?

LVL 76

Expert Comment

by:David Lee
ID: 16679346
Inserting the contents of a Word document in the body is possible, but I strongly recommend using the built-in mail merge capability instead.  Mail merge includes the ability to replace text with data from an Outlook contact.  If you really want to script your own solution, then the key is making the replaceable text unique.  
LVL 11

Author Comment

ID: 16679391
I'm not sure if this makes a difference on what you're saying, but I'm not planning on sending out a bunch of emails all at once with the text replacement.

I get requests for information frequently and I just want to send the same information to each person who requests it when they request it, and personalize it easily.

Can you give me or point me to code that gets close to this?

LVL 76

Accepted Solution

David Lee earned 2000 total points
ID: 16695767
Ok, the code for this is below.  There may be a simpler way to handle the Word document, I'm not a Word expert.  This works by opening the Word document you want to insert, saving it as a temp file in HTML format, then re-open and read it into the body of the email.  

Const wdFormatFilteredHTML = 10

Sub InsertWordDocinMessage()
    Dim olkMsg As Outlook.MailItem, _
        objWordApp As Object, _
        objWordDoc As Object, _
        objFSO As Object, _
        objFile As Object, _
        strPath As String
    'Change the save to path as desired
    strPath = "C:\Temp\"
    Set objWordApp = CreateObject("Word.Application")
    'Change the document path and file name as needed
    Set objWordDoc = objWordApp.Documents.Open("C:\MyFile.doc", , True)
    'Change the temp file name as desired
    objWordDoc.SaveAs strPath & "temp.html", wdFormatFilteredHTML
    objWordDoc.Close False
    Set objWordDoc = Nothing
    Set objWordApp = Nothing
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'Change the temp file name to match the one above
    Set objFile = objFSO.OpenTextFile(strPath & "temp.html")
    Set olkMsg = Application.CreateItem(olMailItem)
    olkMsg.HTMLBody = objFile.ReadAll
    Set objFile = Nothing
    Set objFSO = Nothing
End Sub

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
The core idea of this article is to make you acquainted with the best way in which you can export Exchange mailbox to PST format.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question