Email worksheet with Outlook, get values from opened workbook, from Excel

Question

I have my "MASTER.xlsm" Workbook opened. I run the code from "MASTER.xlsm".

I want to get values from my "MASTER.xlsm" to newly created Outlook email. I am not getting them there.

Is there something wrong with sequence in the code?

Sub EmailWithOutlook()
    Dim oApp As Object
    Dim oMail As Object
    Dim WB As Workbook
    Dim WBBW As Workbook
    Dim FileName As String
    Dim wSht As Worksheet
    Dim shtName As String
    Set WBBW = Workbooks("MASTER.xlsm")
    WBBW.Worksheets("MAIN").Range("D134").Value = variableX
    WBBW.Worksheets("MAIN").Range("D11").Value = variableY
    WBBW.Worksheets("MAIN").Range("D13").Value = variableZ

    Application.ScreenUpdating = False

    ' Make a copy of the active worksheet
    ' and save it to a temporary file
    ActiveSheet.Copy
    Set WB = ActiveWorkbook

    FileName = "My file"
    On Error Resume Next
    Kill "\" & FileName
    On Error GoTo 0
    WB.SaveAs FileName:=Environ$("temp") & "\" & FileName

    'Create and show the Outlook mail item
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    With oMail
        'Uncomment the line below to hard code a recipient
        .To = ""
        'Uncomment the line below to hard code a subject
        .Subject = "My subject | " & variableX & " | " & variableY
        'Uncomment the lines below to hard code a body
        .HTMLBody = "<BODY style=font-size:11pt;font-family:Calibri>Dear Sir/Madam, <br><br> please check this " & _
        variableZ & "" & _
        " and comment if needed.<br> Waiting for your reply ASAP. <br><br> Thank you!</BODY>" & .HTMLBody
        .Attachments.Add WB.FullName
        .Display
    End With

    'Delete the temporary file
    WB.ChangeFileAccess Mode:=xlReadOnly
    Kill WB.FullName
    WB.Close SaveChanges:=False

    'Restore screen updating and release Outlook
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing
End Sub

Add `Option Explicit` you will be forced to first declare undeclared variables. Then you will make those variables have a value. For the future Tools | Options | Editor tab | Checkbox "Require Variable Declaration". — niton, Nov 05 '18 at 21:00

score 1 · Accepted Answer · answered Nov 06 '18 at 08:50

I checked your code, as niton said, you should be to first declare variables e.g. "variableX" "variableY" "variableZ". However, as I understand, you just want to send email from a Excel file. So you can refer to the following code:

Sub test()


Dim strReportName As String
Dim oLook As Object
Dim oMail As Object
Dim olns As Outlook.Namespace
Dim strTO As String
Dim strCC As String
Dim strMessageBody As String
Dim strSubject As String

Set oLook = CreateObject("Outlook.Application")
'Set olns = oLook.GetNamespace("MAPI")
Set oMail = oLook.CreateItem(0)

'*********************** USER DEFINED SECTION ************************
strTO = "chrissparkes@me.com"
strMessageBody = "<---This is an automatically generated email. Please do not respond.---->"
strSubject = "Daily Skip"
'*********************************************************************

With oMail
.To = strTO
 .CC = strCC
 .Body = strMessageBody
 .Subject = strSubject

 '.Attachments.Add "C:\Output Reports\SkipLotReport.xlsx"
 .Display
End With

Set oMail = Nothing
Set oLook = Nothing
'Set olns = Nothing


'DB.Close
'tbloutput.Close
'dbLocal.Close
objWorkbook.Close

'Set objmail = Nothing
'Set DB = Nothing
Set tbloutput = Nothing


Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
Set tbloutput = Nothing
Set dbLocal = Nothing
End Sub

Note: Please reference the Outlook lib.

For more information, please see the link: Sending emails to multiple recipients using VBA

The question states "I want to get values from my "MASTER.xlsm" to newly created Outlook email". — niton, Nov 06 '18 at 21:42

score 0 · Answer 2 · answered Nov 07 '18 at 22:25

You are missing Option Explicit

Option Explicit

' Tools | Options | Editor tab
' Checkbox "Require Variable Declaration"

Sub EmailWithOutlook()

    Dim oApp As Object
    Dim oMail As Object

    Dim WB As Workbook
    Dim WBBW As Workbook

    Dim FileName As String

    Dim wSht As Worksheet
    Dim shtName As String

    Dim variableX As String
    Dim variableY As String
    Dim variableZ As String

    Set WBBW = Workbooks("MASTER.xlsm")

    variableX = "variableX string for example"
    variableY = "variableY string for example"
    variableZ = "variableZ string for example"

    WBBW.Worksheets("MAIN").Range("D134").Value = variableX
    WBBW.Worksheets("MAIN").Range("D11").Value = variableY
    WBBW.Worksheets("MAIN").Range("D13").Value = variableZ

    Application.ScreenUpdating = False

    ' Make a copy of the active worksheet
    ' and save it to a temporary file
    ActiveSheet.Copy
    Set WB = ActiveWorkbook

    FileName = "My file"
    On Error Resume Next
    ' This probably does not do anything. On Error Resume Next strikes again?
    Kill "\" & FileName
    On Error GoTo 0

    WB.SaveAs FileName:=Environ$("temp") & "\" & FileName

    'Create and show the Outlook mail item
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    With oMail
        'Uncomment the line below to hard code a recipient
        .To = ""
        'Uncomment the line below to hard code a subject
        .Subject = "My subject | " & variableX & " | " & variableY
        'Uncomment the lines below to hard code a body
        .HTMLBody = "<BODY style=font-size:11pt;font-family:Calibri>Dear Sir/Madam, <br><br> please check this " & _
        variableZ & "" & _
        " and comment if needed.<br> Waiting for your reply ASAP. <br><br> Thank you!</BODY>" & .HTMLBody
        .Attachments.Add WB.FullName
        .Display
    End With

    'Delete the temporary file
    WB.ChangeFileAccess Mode:=xlReadOnly
    Kill WB.FullName
    WB.Close SaveChanges:=False

    'Restore screen updating and release Outlook
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing
End Sub

Email worksheet with Outlook, get values from opened workbook, from Excel

2 Answers2