+ Reply to Thread
Results 1 to 8 of 8

VBA to send embedded image in email body- NOT USING THE ORGINAL IMAGE SOURCE

  1. #1
    Registered User
    Join Date
    06-10-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    12

    VBA to send embedded image in email body- NOT USING THE ORGINAL IMAGE SOURCE

    Hello,

    I have an interesting issue where I am attemping to embed an image within an email body. I first export these charts as .GIF images to a shared drive that myself and other users have access to. When I send these emails to myself, I can see the charts perfectly fine. However, when I send these charts to other users, all they can see are broken images (boxes with X's in them). All of the recievers of the message have confirmed read/write access to the shared file location, and can open up the images at that location as well as the attachments from the email- they just can't see the embedded images within the email itself.

    My thought is that for some reason the images arent sourcing correctly to where they are stored. I can foresee two, maybe three potential solutions to this problem, although I don't know how to accomplish them- hopefully all of you fine folks can help.
    Potential solution 1: Export the .GIF images to temporary internet files that can be accessed by others INSTEAD of relying on a shared drive.
    Potential solution 2: Embed the images BUT once they are in the email, remove the links to the files they originate from, leaving just a static embedded image.
    Potential solution 3: Somehow embed the images not from the orignal image source, but from the attachments themselves. I.E attach all relevant GIF's first, then embed them not from a file location, but from the actual attachment.

    Any help is much appreciated, hopefully i was clear and one of my proposed solutions is technically feasible. Unfortunately, I dont have the knowledge to code them out.

    Please see a snippet of code below. Some things such as example, test, etc have been puposefully changed from their true values.
    Sub MultipleSendOffCharts()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim Fname1 As String
    Dim FnameRoot As String
    Dim bdy As String

    'Turn on Outlook for Excel
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    'Save off each chart to where you will
    FnameRoot = "S:\Public\Example\"
    Fname1 = FnameRoot & "test.gif"
    With ActiveWorkbook.Worksheets("ExampleWorksheet").ChartObjects("ExampleChart")
    .Activate
    .Chart.Export FileName:=Fname1, FilterName:="GIF"
    End With

    'send E-mail
    With OutMail
    .To = "[email protected]"
    .CC = ""
    .BCC = ""
    .Subject = "Sample"
    .Attachments.Add Fname1

    'HTML body for image
    bdy = "<img src='S:\Public\Example\test.gif'>"

    'Now add it into body
    .HTMLBody=bdy

    'send email
    .Send

    End With
    Set OutMail = Nothing
    Set OutApp = Nothing

    ActiveWorkbook.EnvelopeVisible = False

    'Turn off Excel save notifcation
    Application.DisplayAlerts = False

    End Sub

    Appreciate the help guys
    Last edited by Coreyusa; 06-27-2013 at 03:38 PM.

  2. #2
    Forum Contributor
    Join Date
    06-20-2013
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2007
    Posts
    106

    Re: VBA to send embedded image in email body- NOT USING THE ORGINAL IMAGE SOURCE

    This post answers your question. I have a sample file in the forum post.

    http://www.excelforum.com/excel-prog...html?p=3297915

    Look at post #27 for the final embedding into Outlook using a .jpg.

  3. #3
    Registered User
    Join Date
    06-10-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: VBA to send embedded image in email body- NOT USING THE ORGINAL IMAGE SOURCE

    Thanks for your response, but looking through your code, I don't believe that is what I was looking to do.
    Looks like you are sending an embedded range, which I can already do.
    I want to send an embedded image, without explicitly referencing the image source in a shared folder each time the image opens. I can see my embedded images perfectly- the people I send it to cannot.

  4. #4
    Registered User
    Join Date
    06-10-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: VBA to send embedded image in email body- NOT USING THE ORGINAL IMAGE SOURCE

    In fact, I don't even really care neccessarily about attaching the images to the email (I usually just omit the .Attachments.Add line). I only did that for debugging purposes.
    Once again, I can see the embedded images perfectly, the others I send it to cannot. Everytime the email is opened, I believe the embedded images refresh, and the end recipients for some reason don't load it. Ideally, if I could remove this link and just make the image static, the problem would be solved. But the other two solutions I offered above could work as well I think hypothetically, I just don't know how to code them properly.

    Also GIF looks better than JPG. Also tried with JPG, same result: I could view embedded charts, others could not
    Last edited by Coreyusa; 06-27-2013 at 05:14 PM.

  5. #5
    Registered User
    Join Date
    06-10-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: VBA to send embedded image in email body- NOT USING THE ORGINAL IMAGE SOURCE

    Update: using the "<img src=cid: Fname1.gif></img>" works once the image is attached so that the other recipients can see the embedded image. Unfortunately, when I try to add multiple images, each with a new line like the one above, i.e.

    bdy="<img src=cid: Fname1.gif></img>"
    bdy= bdy & "<img src=cid: Fname2.gif></img>"

    All I get is the first image twice. Can you only have one CID tag? How can I properly reassign so that the email embeds image 1 and 2, instead of image 1 twice?

  6. #6
    Registered User
    Join Date
    06-10-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: VBA to send embedded image in email body- NOT USING THE ORGINAL IMAGE SOURCE

    Now I feel silly. Get rid of space between cid: and file name.
    So instead of cid: Fname1.gif, it is cid:Fname1.gif.
    Subtle difference.
    As a last question, is there anyway to delete the image attachments after having embedded them in the email?
    After checking with the people I am emailing, apparently I can see the attachments, but they only see the embedded images (this is what I want). I guess that is fine, but would prefer if the images weren't attached at all after being embedded (on my end).

  7. #7
    Forum Contributor
    Join Date
    06-20-2013
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2007
    Posts
    106

    Re: VBA to send embedded image in email body- NOT USING THE ORGINAL IMAGE SOURCE

    Post #27 works. We solved this image issue yesterday. Check out this forum...You have to Attach then Save then cid:

    Just look at my code:

    Please Login or Register  to view this content.
    http://www.excelforum.com/excel-prog...html?p=3297915

  8. #8
    Registered User
    Join Date
    06-10-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: VBA to send embedded image in email body- NOT USING THE ORGINAL IMAGE SOURCE

    You are correct, I got my code to work. Also, attachments randomly disappeared, which is good.
    Incase anyone who reads this in the future needs a manual way to delete attachments, I believe you can use Attachments.Items(i).Delete
    Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1