Sending mail with text and image

vsk17

New member
Joined
Jan 2, 2018
Messages
3
Reaction score
0
Points
0
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
 
Hi and welcome

please do not hijack threads but create one of your own.
I did it for you this time - Thanks
 
Sure... Thank you...will do that going forward
 
.
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
 

Attachments

  • WORKS Mail Em w PDF Attach and Pic In Msg Body .xlsm
    20.8 KB · Views: 34
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
 
Back
Top