CODEWITHSUNDEEP
excelvbaemailoutlook
libertyexcel
libertyexcel

Reputation: 1

How to send e-mail from excel with macro that started in outlook?

The macro this comes from starts in Outlook when a message is received with a specific subject. Excel does some calculations to the report and then I need it to send an e-mail to someone else. I have been able to get it to work completely if I start the macro manually from excel but when it starts automatically I get an error when trying to create the e-mail @ "Set OutlookMail".
EDIT: "Run-time error '91': Object variable or With block variable not set" is the error I receive at "Set OutlookMail = OutlookApp.CreateItem(0)"

XL_hh_mck_weekly is the code from Outlook that triggers the excel macro hh_mck_weekly. There are several steps between but those do not involve outlook. This is the code being used:

Sub XL_hh_mck_weekly()
Dim Item As Outlook.MailItem
Dim olOutmail As Outlook.MailItem

If xlApp Is Nothing Then Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

With CreateObject("Excel.Application")
    .Workbooks.Open (filepath & "PERSONAL.xlsb")
    .Workbooks.Open (filepath & "hhweekly.csv")
    .Visible = False

    ' Ensure Autocalculation is on
    .Calculation = -4105 ' xlCalculationAutomatic

    .DisplayAlerts = False
    .Run "'PERSONAL.xlsb'!hh_mck_weekly"

    ' Wait until calculation is done
    Do Until .CalculationState = 0   ' xlDone
        DoEvents
    Loop

End With

Sub hh_mck_weekly(nm As String)

Dim OutlookApp As Outlook.Application
Dim OutlookMail As Object
Dim OutlookMail2 As Object



   If OutlookApp Is Nothing Then Set OutlookApp = CreateObject("Outlook.Application")



Set OutlookMail = OutlookApp.CreateItem(0)
Set OutlookMail2 = OutlookApp.CreateItem(0)

'   Open Need Category
    If IsOpen("NeedCategory.xls") = True Then

'     Get category from Mckesson
      With OutlookMail
        .To = address
        .Subject = "Category"
        .Body = "Byron, I need the category for these items.  Thanks"
        .Attachments.Add (filepath & "NeedCategory.xls")
        .Display
        .Send
    End With

End If


With OutlookMail2
    .To = address
    .Subject = "Weekly Mckesson Report"
    .Body = "Thanks"
    .Attachments.Add (filepath & nm)
    .Display
End With

Set OutlookMail = Nothing
Set OutlookApp = Nothing

End Sub

Upvotes: 0

Views: 172

Answers (1)

niton
niton

Reputation: 9179

The code in the question does not appear to be the code in use. There is a parameter required in Sub hh_mck_weekly(nm As String).

This demonstrates how to create a mailitem in Excel code called from Outlook.

Code for Outlook

Option Explicit ' Consider this mandatory
' Tools | Options | Editor tab
' Require Variable Declaration
' If desperate declare as Variant

Sub createMailitem_InExcel()

' code for Outlook

Dim xlApp As Excel.Application
Dim filePath As String

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
On Error GoTo 0

If xlApp Is Nothing Then Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

With xlApp
    
    ' adjust as needed
    filePath = "C:\Users\ -userid- \AppData\Roaming\Microsoft\Excel\XLSTART\"

    .Workbooks.Open (filePath & "PERSONAL.xlsb")
    
    .Run "'PERSONAL.xlsb'!test"
    
End With
End Sub

Code for PERSONAL.xlsb

Sub test()

' code for PERSONAL.xlsb

Dim OutlookApp As Outlook.Application
Dim OutlookMail As Outlook.MailItem

' No need to verify if already open. There can only be one Outlook instance.
Set OutlookApp = CreateObject("Outlook.Application")

Set OutlookMail = OutlookApp.CreateItem(0)

OutlookMail.Display

End Sub

Upvotes: 0

Related Questions