Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 144
  • Last Modified:

Email template with variation in subject and attachment

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
mmcompact
Asked:
mmcompact
  • 3
  • 2
1 Solution
 
David LeeCommented:
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
 
mmcompactAuthor Commented:
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
 
David LeeCommented:
I can add code that reads in from the file or you can copy and paste.  You're choice.
0
 
mmcompactAuthor Commented:
Read from file will be better. thanks
0
 
David LeeCommented:
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 Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now