Excel - Auto create email

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Excel - Auto create email

Post by VegasNath »

I am auto creating emails (outlook) in excel. I know how to auto attach an excel file to an email, but is it possible to attach another email to the email that I am creating? My outlook knowledge is very basic. The email would be stored in one of my outlook folders.
:wales: Nathan :uk:
There's no place like home.....

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel - Auto create email

Post by HansV »

Here is some sample code; you'll have to modify it for your situation, of course.

Code: Select all

Dim objOutlook As Outlook.Application
Dim objItem1 As Outlook.ApplicationMailItem
Dim objItem2 As Outlook.ApplicationMailItem
Dim objNsp As Outlook.ApplicationNameSpace
Dim objFolder As Outlook.MAPIFolder

' Start Outlook
Set objOutlook = CreateObject("Outlook.Application")
' Create new message
Set objItem1 = objOutlook.CreateItem(olMailItem)
' Get reference to Outlook namespace
Set objNsp = objOutlook.GetNamespace("MAPI")
' Get reference to folder
Set objFolder = objNsp.Folders("Personal Folders").Folders("Drafts")
' Get reference to an existing message (by subject)
Set objItem2 = objFolder.Items("Test")
' Attach to the new message
objItem1.Attachments.Add objItem2, olEmbeddeditem
Note: in Outlook 2007 or later, declare objFolder as Outlook.Folder instead of as Outlook.MAPIFolder.
Best wishes,
Hans

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Excel - Auto create email

Post by VegasNath »

Thankyou Hans, I was able to adapt the code to suit.

May I enquire about an alternative method: Instead of creating a new blank email, is it possible to open an existing email and tag a new email onto that?
:wales: Nathan :uk:
There's no place like home.....

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel - Auto create email

Post by HansV »

Yes, of course. Simply change the line

Set objItem1 = objOutlook.CreateItem(olMailItem)

with lines resembling that for objItem2, for example

' Get reference to folder
Set objFolder = objNsp.Folders("Personal Folders").Folders("'Financial")
' Get reference to an existing message (by subject)
Set objItem1 = objFolder.Items("July Invoice")

You can then attach objItem2 the same way as in the first example.
Best wishes,
Hans

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Excel - Auto create email

Post by VegasNath »

Hans, I have managed to get it to partially work, but I am losing the text of the original mail. I would like the text that I create to be input into the original email above the text in the original, almost like a reply or forward.
:wales: Nathan :uk:
There's no place like home.....

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel - Auto create email

Post by HansV »

You now have a line similar to

.HTMLBody = strbody

Change this to

.HTMLBody = strBody & "<br><br>" & .HTMLBody
Best wishes,
Hans

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Excel - Auto create email

Post by VegasNath »

Thankyou.
:wales: Nathan :uk:
There's no place like home.....

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Excel - Auto create email

Post by VegasNath »

Hans,
Thanks for your help thus far. I have come across a few problems in testing.

What I am trying to do is: I have an email in my inbox that I wish to use as a template for a response. IOW, I will be sending an individual specific email to many people, but I want to tag each of those emails to the front of an already existing email.

The code below works with 2 faults. If i use .send, this is OK, however, If I use .Display (my preference so I can check the emails first), there is no 'send' option on the displayed email.
Also, when one email is sent, the template email disappears from my inbox, therefore I am unable to send a second mail.

I would appreciate any suggestions as to how I overcome these issues..

Code: Select all

'Option Explicit

Function GetBoiler(ByVal sFile As String) As String
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
    ts.Close
End Function

Sub CreateEmail()

Dim fso As Object, fld As Object
Dim strDateToday As String, strDateWork As String
Dim strParnt As String, strPath As String, strFile As String
Dim strTitle As String, strbody As String
Dim strSig As String, Signature As String, sigName As String
Dim OutApp As Object
Dim OutMail As Object
Dim r As Long
Dim m As Long
Dim strList As String
Dim objItem2 As Object
Dim objNsp As Object
Dim objFolder As Object

Set fso = CreateObject("Scripting.FileSystemObject")
'Set fld = fso.GetFolder(ThisWorkbook.Path)
    
strTitle = "Blah Blah (Ref " & Range("A5") & ")"

'sigName = InputBox(Prompt:="Enter your Signature Name?", Title:="Signature Name?", Default:="Nathan New")
sigName = "Nathan New"

'XP Signature
strSig = "C:\Documents and Settings\" & Environ("username") & "\Application Data\Microsoft\Signatures\" & sigName & ".htm"

'Vista Signature
'strSig = "C:\Users\" & Environ("username") & "\AppData\Roaming\Microsoft\Signatures\" & sigName & ".htm"

    If Dir(strSig) <> "" Then
        Signature = GetBoiler(strSig)
    Else
        Signature = Application.UserName & " (Insert signature here)"
    End If
    
With Worksheets("Email Generator")
    m = .Cells(Rows.Count, 1).End(xlUp).Row
    strList = "<ul>" & vbCrLf
        For r = 5 To m
            strList = strList & "<li>Ref " & .Cells(r, 1) & "-" & .Cells(r, 7) & " - " & .Cells(r, 8) & " - " & .Cells(r, 9) & "</li>" & vbCrLf
        Next r
    strList = strList & "</ul>"
End With
        
strbody = "Hi " & Range("F5") & "," & "<br><br>" & _
            "Blah blah." & "<br><br>" & _
            "blah...." & "<br><br>" & _
            strList & _
            "more blah.." & "<br><br>" & _
            "Where there have been no changes, please provide a nil return." & "<br><br>" & _
            "Kind Regards," & "<br><br>" & _
            Signature & "<br>"
            
            
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    
    'Get reference to Outlook namespace
        Set objNsp = OutApp.GetNamespace("MAPI")
    'Get reference to folder
        Set objFolder = objNsp.Folders("Mailbox - Fred Bloggs").Folders("Inbox")
    'Get reference to an existing message (by subject)
        Set objItem2 = objFolder.Items("Existing email")
        
    Set OutMail = objFolder.Items("Existing email")

    With OutMail
        .To = Range("E5")
        .CC = "someone"
        .Subject = strTitle
        '.HTMLBody = strbody
        .HTMLBody = strbody & "<br><br>" & .HTMLBody
        '.Attachments.Add strFile
        '.Attachments.Add objItem2, olEmbeddeditem
        '.Display
        .send
        '.ReadReceiptRequested = True
    End With

    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

:wales: Nathan :uk:
There's no place like home.....

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel - Auto create email

Post by HansV »

I'd create a new message using

Set Outmail = OutApp.CreateItem(olMailItem)

and then set its HTMLBody property to the new string followed by the HTMLBody of the existing e-mail:

.HTMLBody = strbody & "<br><br>" & objItem2.HTMLBody
Best wishes,
Hans

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Excel - Auto create email

Post by VegasNath »

Perfecto.
Mucho Gracias!
:wales: Nathan :uk:
There's no place like home.....

User avatar
Goshute
3StarLounger
Posts: 397
Joined: 24 Jan 2010, 19:43
Location: Salt Lake City, Utah, USA

Re: Excel - Auto create email

Post by Goshute »

VegasNath wrote:What I am trying to do is: I have an email in my inbox that I wish to use as a template for a response.
Without a full understanding of what you are attempting, I wonder if this email that you want to be used as a template can be saved an OST file and created in Outlook using the .CreateItemFromTemplate Method - see http://msdn.microsoft.com/en-us/library ... 12%29.aspx" onclick="window.open(this.href);return false;
VegasNath wrote:... but I want to tag each of those emails to the front of an already existing email.
Huh? You lost me here - are you saying in effect that you want to Forward an existing message? There's a MailItem.Forward Method, if that would be of use.
Goshute
I float in liquid gardens

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Excel - Auto create email

Post by HansV »

VegasNath wrote:Mucho Gracias!
¡Le invitamos!
Best wishes,
Hans

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Excel - Auto create email

Post by VegasNath »

[quote="Goshute"][/quote]

Thanks for your response, I have what I need for now, however that may be a better long term method so I will investigate that further. :cheers:
:wales: Nathan :uk:
There's no place like home.....