Solved

Email template with variation in subject and attachment

Posted on 2011-03-10
7
134 Views
Last Modified: 2013-11-05
I need to send some emails everyday with almost the same format:
To: someone1@gmail.com
CC: someone2@gmail.com, someone3@gmail.com

Content:
"some text..."

The only difference are:
Subject: Company Report 03-11
Attachment: 2011-03-11 Company Report.xls

Both of the dates will be today's date, and the excel file is already in the same folder "H:\Reports" with correct name

How can I use some VBA to do this automatically? I don't need to send it right away, have to edit something before sending.
0
Comment
Question by:mmcompact
  • 3
  • 2
7 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 35099145
Hi, mmcompact.

This should do it.
Sub SendCannedMessage()
    Dim olkMsg As Outlook.MailItem, strFilename As String
    strFilename = Format(Date, "yyyy-mm-dd") & " Company Reports.xls"
    Set olkMsg = Application.CreateItem(olMailItem)
    With olkMsg
        .To = "someone1@gmail.com"
        .CC = "some2@gmail.com; someone3@gmail.com"
        .HTMLBody = "Some text ..."
        .Subject = "Company Report " & Format(Date, "mm-dd")
        .Attachments.Add "H:\Reports\" & strFilename
        .Recipients.ResolveAll
        .Display
    End With
    Set olkMsg = Nothing
End Sub

Open in new window

0
 

Author Comment

by:mmcompact
ID: 35099662
BlueDevilFan:

Thanks. The HTMLbody is actually a quite long html file. Can I still copy and paste the content there or I need to link to the actual file?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 35100039
I can add code that reads in from the file or you can copy and paste.  You're choice.
0
 

Author Comment

by:mmcompact
ID: 35100404
Read from file will be better. thanks
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 35100659
Try this.
Sub SendCannedMessage()
    Dim olkMsg As Outlook.MailItem, strFilename As String, objFSO as Object, objFil As Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'Change the file name on the next line'
    Set objFil = objFSO.OpenTextFile("C:\SomeFileName.htm")
    strFilename = Format(Date, "yyyy-mm-dd") & " Company Reports.xls"
    Set olkMsg = Application.CreateItem(olMailItem)
    With olkMsg
        .To = "someone1@gmail.com"
        .CC = "some2@gmail.com; someone3@gmail.com"
        .HTMLBody = objFil.ReadAll
        .Subject = "Company Report " & Format(Date, "mm-dd")
        .Attachments.Add "H:\Reports\" & strFilename
        .Recipients.ResolveAll
        .Display
    End With
    objFil.Close
    Set objFil = Nothing
    Set objFSO = Nothing
    Set olkMsg = Nothing
End Sub

Open in new window

0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Create high volume marketing opportunities using email signatures with these top 10 DOs and DON'Ts of email signature marketing.
Sometimes Outlook might have problems sending a message. There may be various causes- corrupted PST, AV scanner etc. The message, instead of going to the Sent Items folder, sits in the Outbox indefinitely. To remove it you can use a free tool cal…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
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: …

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now