Results 1 to 5 of 5

Thread: Sending mail with text and image

  1. #1

    Sending mail with text and image



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I am new to VBA and would require some help. I am trying to send an through excel which has some text in the body of the email and also an image. I am able to add the Image to the email using the outlook application 2016. I am unable to add the text before the image. Could you please help me with the same.

    Here is the summary below

    Email Body :

    Some text
    Some text
    Some text

    Image

    Please note that the email text and also image is in the same excel spreadsheet.

    Let me know if you need any information.

    Thanks in advance
    Vinay

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,673
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hi and welcome

    please do not hijack threads but create one of your own.
    I did it for you this time - Thanks
    Thank you Ken for this secure forum.

  3. #3
    Sure... Thank you...will do that going forward

  4. #4
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    223
    Articles
    0
    Excel Version
    2007
    .
    Code:
    Option Explicit
    
    
    
    
    Sub EmailWithOutlook()
        Dim oApp As Object
        Dim oMail As Object
        Dim WB As Workbook
        Dim FileName As String
        Dim wSht As Worksheet
        Dim shtName As String
        Dim ThisFile As String
        Dim oByValue
        
        'ThisFile = "C:\Users\My\Desktop\gun_maintenance.pdf"    '<-- This line is required to add a document as attachment to email.
                                                                '<-- change path as required
                                                                
        Application.ScreenUpdating = False
    
    
        'Create and show the Outlook mail item
        Set oApp = CreateObject("Outlook.Application")
        Set oMail = oApp.CreateItem(0)
        
        With oMail
            
            'I need this to pull the "to" from B1
            .To = Sheets("Sheet1").Range("B1").Value                          '<--- Settings Sheet name here is Sheet1. Change as needed
            
            'I need this to pull the "Subject" from Settings:A2
            .Subject = Sheets("Sheet1").Range("B2").Value                       '<--- Settings Sheet name here is Sheet1. Change as needed
            
            'I need this to pull the "body" from Settings:B3
            .Body = Sheets("Sheet1").Range("B3").Value                          '<--- Settings Sheet name here is Sheet1. Change as needed
            
            '.Attachments.Add ThisFile   '<-- This line is required to add a document as attachment to email.
            
            'NEXT LINE: add the image in hidden manner, position at 0 will make it hidden. This line required for image to show.
            .Attachments.Add "C:\Users\logit\Desktop\pics\Bear-stuffs.jpg", oByValue, 0
        
            'Now add it to the Html body using image name
            'change the src property to 'cid:your image filename'
            'it will be changed to the correct cid when its sent.
            .HTMLBody = .HTMLBody & "<br><B>Embedded Image:</B><br>" _
                        & "<img src='cid:Bear-stuffs.jpg'" & "width='375' height='486'><br>" _
                        & "<br>Best Regards, <br>Your name here</font></span>"
            .Display
        End With
    
    
        'Restore screen updating and release Outlook
        Application.ScreenUpdating = True
        Set oMail = Nothing
        Set oApp = Nothing
    End Sub
    Attached Files Attached Files

  5. #5
    Hi , Thank you so much for your help....this is working perfectly.

    I had another clarification, I have the body of the email in multiple cells. I have tried the below mentioned modification from the above code and it is not taking the range. Kindly help.

    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    Set rng = Sheets("mail").Range("B2:B9").SpecialCells(xlCellTypeVisible)
    With oMail

    .To = Sheets("mail").Range("B1").Value
    .Subject = Sheets("mail").Range("B10").Value
    .HTMLBody = Sheets("mail").RangetoHTML(rng)
    .CC = Sheets("mail").Range("B11").Value

    Thanks in advance
    Vinay

Posting Permissions

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