MS Office Forum / Outlook / Programming VBA / December 2006
newbie creating email VBA question
|
|
Thread rating:  |
Keith - 20 Dec 2006 18:40 GMT I'm using Office 2003 on WinXP.
I have several gif files that I create in Excel (from Excel ranges) and within Excel, I want to create an email that attaches these GIF files and sends them to the target recipient. I have this working (in theory) when sending to myself, but there are a few things not working properly, and I'm hoping someone here can help- even though the code below is run from Excel, folks here are more likely to be able to spot syntax issues related to the code that manipulates Outlook.
Goal: to have the images show up inline, e.g. visible when the email is opened (currently it shows up as an attached file that then needs to be opened). My default outlook email editor is Word2003, and I can do this by creating a new email, and inserting an image:
Selection.InlineShapes.AddPicture FileName:="C:\AEPARDAV1.gif", LinkToFile _ :=False, SaveWithDocument:=True
However, I'm not sure that Word is part of the equation when I run my code directly from Excel:
Function Mail_Selection_Range_Outlook_Body(SendToName As String, SendFileCount As Integer) ' code adapted from http://www.rondebruin.nl/mail/ ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007
Dim rng As Range Dim OutApp As Object Dim OutMail As Object
With Application .EnableEvents = False .ScreenUpdating = False End With
Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0)
FName = "c:\" & SendToName FExt = ".gif"
On Error Resume Next With OutMail .To = "rucksk" 'SendToName .CC = "" .BCC = "" .Subject = "Roadblocks data update request" .Body.Add = "Please respond to this email, and include comments and updates directly under each row" '.Body.Add = Chr(13) For n = 1 To SendFileCount .Attachments.Add (FName & CStr(n) & FExt) ' this is where it adds as an attachment instead of inline '.Body.Add = Chr(13) Next .Send 'or use .Display End With On Error GoTo 0
With Application .EnableEvents = True .ScreenUpdating = True End With
Set OutMail = Nothing Set OutApp = Nothing End Function
Sue Mosher [MVP-Outlook] - 20 Dec 2006 19:15 GMT Very important question: Is WordMail your Outlook editor? If not, you can't use Word methods. If it is, then use Outlook's Inspector.WordEditor method to return a Word.Document object that you can then use with methods like AddPicture, e.g.:
strFile = "C:\AEPARDAV1.gif" Set objInsp = OutMail.GetInspector Set objDoc = objInsp.WordEditor Set objWord = objDoc.Application Set objSel = objWord.Selection If objMsg.BodyFormat <> olFormatPlain Then objSel.InlineShapes.AddPicture strFile, False, True End If
 Signature Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx
> I'm using Office 2003 on WinXP. > [quoted text clipped - 65 lines] > Set OutApp = Nothing > End Function Keith - 20 Dec 2006 19:57 GMT Sue-
Thank you for your response. My preference would be to use code that is independent of the default editor (e.g. limit it to Outlook only- I'm not sure if this is possible?), as the workbook will be maintained by a support team, and I don't have control over what they use for their editor- some may use Word, some may not. I copied over the Word code just to show what (on my machine, anyway) was able to generate the desired result- but it may not be the desired way to reach that result.
If there is a way to attach inline images directly without referencing Word, that would be preferable- even if I wrote conditional code to determine if Word is the editor for whoever is using the file, the best I could do is kill the macro, because I'd want to avoid sending emails with the images as attachments.
Perhaps there is a parameter of Attachments.Add, or a way to add a graphic file as part of the mail body?
Many thanks, Keith
Very important question: Is WordMail your Outlook editor? If not, you can't use Word methods. If it is, then use Outlook's Inspector.WordEditor method to return a Word.Document object that you can then use with methods like AddPicture, e.g.:
strFile = "C:\AEPARDAV1.gif" Set objInsp = OutMail.GetInspector Set objDoc = objInsp.WordEditor Set objWord = objDoc.Application Set objSel = objWord.Selection If objMsg.BodyFormat <> olFormatPlain Then objSel.InlineShapes.AddPicture strFile, False, True End If
 Signature Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx
> I'm using Office 2003 on WinXP. > [quoted text clipped - 69 lines] > Set OutApp = Nothing > End Function Sue Mosher [MVP-Outlook] - 20 Dec 2006 20:16 GMT There is another method, but it requires CDO, which is an optional component, or Redemption, a third-party component, and is quite a bit more complex. See http://www.outlookcode.com/d/code/htmlimg.htm
 Signature Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx
> Sue- > [quoted text clipped - 31 lines] > objSel.InlineShapes.AddPicture strFile, False, True > End If
>> I'm using Office 2003 on WinXP. >> [quoted text clipped - 69 lines] >> Set OutApp = Nothing >> End Function Keith - 20 Dec 2006 20:40 GMT Wow, I wouldn't have been able to tackle this on my own, but with the code snippet you reference, I'll try to make sure I understand it, then adapt it and see if I can get it to work. Many, many thanks!! Keith
There is another method, but it requires CDO, which is an optional component, or Redemption, a third-party component, and is quite a bit more complex. See http://www.outlookcode.com/d/code/htmlimg.htm
 Signature Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx
> Sue- > [quoted text clipped - 38 lines] > objSel.InlineShapes.AddPicture strFile, False, True > End If
> "Keith" <fake_address@not_a_real_address.org.net.com> wrote in message > news:Ok0CYZGJHHA.1252@TK2MSFTNGP02.phx.gbl... [quoted text clipped - 76 lines] >> Set OutApp = Nothing >> End Function Keith - 21 Dec 2006 21:47 GMT Sue (and others)-
I've tried to adapt the code provided below, and have been very impressed once I figured out how to get it integrated with my code that cycles through to create the individual emails. I now have two challenges that I think are both easy for someone who knows Outlook (e.g. not me);
- using the following code doesn't set a recipient, but I think I can do that with some variation of: l_Msg.to = SendToName
I'll often have more than one attachment (see the loop below) and only the first one gets translated into an in-line image instead of an attachment. I'd also like to insert a carriage return (or two) between each image, so it is easy for the recipient to reply and have space to type between the images in their response. I've marked the relevant parts of code with ** below :)
I'd also like to set a recipient, but I think I can do that with some variation of: l_Msg.to = SendToName
Many thanks, Keith
> There is another method, but it requires CDO, which is an optional > component, or Redemption, a third-party component, and is quite a bit more > complex. See http://www.outlookcode.com/d/code/htmlimg.htm My slightly adapted code, based on the referenced web page:
Function EmbeddedHTMLGraphicDemo(SendToName As String, SendFileCount As Integer) ' adapted from http://www.outlookcode.com/d/code/htmlimg.htm ' Outlook objects Dim objApp As Outlook.Application Dim l_Msg As MailItem Dim colAttach As Outlook.Attachments Dim l_Attach As Outlook.Attachment Dim oSession As MAPI.Session ' CDO objects Dim oMsg As MAPI.Message Dim oAttachs As MAPI.Attachments Dim oAttach As MAPI.Attachment Dim colFields As MAPI.Fields Dim oField As MAPI.Field
Dim strEntryID As String
' create new Outlook MailItem Set objApp = CreateObject("Outlook.Application") Set l_Msg = objApp.CreateItem(olMailItem) ' add graphic as attachment to Outlook message ' change path to graphic as needed Set colAttach = l_Msg.Attachments
FName = "c:\" & SendToName FExt = ".gif"
'loop to add each graphical file For n = 1 To SendFileCount Set l_Attach = colAttach.Add(FName & CStr(n) & FExt) '** how can I add (inline) a few hard returns here? ** Next
l_Msg.Close olSave strEntryID = l_Msg.EntryID Set l_Msg = Nothing ' POSITION CRITICAL you must dereference the ' attachment objects before changing their properties ' via CDO Set colAttach = Nothing Set l_Attach = Nothing
' initialize CDO session On Error Resume Next Set oSession = CreateObject("MAPI.Session") oSession.Logon "", "", False, False
' get the message created earlier Set oMsg = oSession.GetMessage(strEntryID) ' set properties of the attached graphic that make ' it embedded and give it an ID for use in an <IMG> tag Set oAttachs = oMsg.Attachments
' ** to make all images inline- should I just loop and assume they are all 1 to SendFileCount? ** ' ** I don't understand what this block does :-/ ** 'e.g. 'For q = 1 to SendFileCount 'Set oAttach = oAttachs.Item(q) 'Set colFields = oAttach.Fields 'Set oField = colFields.Add(CdoPR_ATTACH_MIME_TAG, "image/jpeg") 'Set oField = colFields.Add(&H3712001E, "myident") 'oMsg.Fields.Add "{0820060000000000C000000000000046}0x8514", 11, True 'next q
'existing code that only changes the first attachment Set oAttach = oAttachs.Item(1) Set colFields = oAttach.Fields Set oField = colFields.Add(CdoPR_ATTACH_MIME_TAG, "image/jpeg") Set oField = colFields.Add(&H3712001E, "myident") oMsg.Fields.Add "{0820060000000000C000000000000046}0x8514", 11, True oMsg.Update
' get the Outlook MailItem again Set l_Msg = objApp.GetNamespace("MAPI").GetItemFromID(strEntryID) ' add HTML content -- the <IMG> tag l_Msg.HTMLBody = "<IMG align=baseline border=0 hspace=0 src=cid:myident>" l_Msg.Close (olSave) l_Msg.Display
' clean up objects Set oField = Nothing Set colFields = Nothing Set oMsg = Nothing oSession.Logoff Set oSession = Nothing Set objApp = Nothing Set l_Msg = Nothing End Function
Sue Mosher [MVP-Outlook] - 27 Dec 2006 13:13 GMT > I'll often have more than one attachment (see the loop below) and only the > first one gets translated into an in-line image instead of an attachment. You'd need to give each attachment its own unique identifier in the &H3712001E field, not use "myident" for every one.
> I'd also like to insert a carriage return (or two) between each image Use <br> or <p></p> HTML tags.
 Signature Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx
> Sue (and others)- > [quoted text clipped - 116 lines] > Set l_Msg = Nothing > End Function
|
|
|