Email from outlook with Excel cell value in subject

Question

How do I send Email from outlook that contains cell value in the subject from an excel file?

I found every possible code to send email from excel but i really want to have the email open and in subject to have a cell value from an excel file.

And all this done from Outlook not from Excel. Thank you.

:) i don't find how can outlook look in an excel file for the range. — wittman, Mar 03 '16 at 10:31
Apologies, I thought you had the WB open also, you'll need to either open the workbook or query it by excel. Look at http://stackoverflow.com/questions/5094624/outlook-vba-open-excel then you can use Workbook.Worksheet.Range(address).value to add. — Nathan_Sav, Mar 03 '16 at 11:12

0m3r · Accepted Answer · 2016-03-04T03:42:07.650

See Example below

Option Explicit
Sub Cell_Value_in_Subject()
    Dim olItem As Outlook.MailItem
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSht As Excel.Worksheet
    Dim sPath As String

    sPath = "C:\Temp\Book1.xlsx" '<- Update

'   // Excel
    Set xlApp = CreateObject("Excel.Application")
'   // Workbook
    Set xlBook = xlApp.Workbooks.Open(sPath)
'   // Sheet
    Set xlSht = xlBook.Sheets("Sheet1")

    Debug.Print xlSht.Range("A1") '<- Print Value in immediate window

'   // Create e-mail Item
    Set olItem = Application.CreateItem(olMailItem)

    With olItem
        .To = "Om3r@Email.com"
        .Subject = xlSht.Range("A1")
        .HTMLBody = xlSht.Range("A2") & " is Cell Value"
        .Display
    End With

'   // Close
    xlBook.Close SaveChanges:=True
'   // Quit
    xlApp.Quit

    '// CleanUp
    Set xlApp = Nothing
    Set xlBook = Nothing
    Set xlSht = Nothing
    Set olItem = Nothing

End Sub

Email from outlook with Excel cell value in subject

1 Answers1

Linked

Related