Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Outlook / Programming VBA / December 2006

Tip: Looking for answers? Try searching our database.

newbie creating email VBA question

Thread view: 
Enable EMail Alerts  Start New Thread
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.