Excel - Auto create email
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Excel - Auto create email
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.
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78457
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel - Auto create email
Here is some sample code; you'll have to modify it for your situation, of course.
Note: in Outlook 2007 or later, declare objFolder as Outlook.Folder instead of as Outlook.MAPIFolder.
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
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Excel - Auto create email
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?
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?
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78457
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel - Auto create email
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.
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
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Excel - Auto create email
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.
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78457
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel - Auto create email
You now have a line similar to
.HTMLBody = strbody
Change this to
.HTMLBody = strBody & "<br><br>" & .HTMLBody
.HTMLBody = strbody
Change this to
.HTMLBody = strBody & "<br><br>" & .HTMLBody
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Excel - Auto create email
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..
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
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78457
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel - Auto create email
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
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
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
-
- 3StarLounger
- Posts: 397
- Joined: 24 Jan 2010, 19:43
- Location: Salt Lake City, Utah, USA
Re: Excel - Auto create email
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: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.
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.VegasNath wrote:... but I want to tag each of those emails to the front of an already existing email.
Goshute
I float in liquid gardens
I float in liquid gardens
-
- Administrator
- Posts: 78457
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Excel - Auto create email
[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.
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.
Nathan
There's no place like home.....
There's no place like home.....