newbie creating email VBA question

[Keith]

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]]

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

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Keith]

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

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Sue Mosher [MVP-Outlook]]

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

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Keith]

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

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[Keith]

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]]

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.
--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers