CODEWITHSUNDEEP
excelvbaoutlook
Dharmendra
Dharmendra

Reputation: 129

Error While Sending Email through Outlook from Excel VBA

I have write a macro in outlook and excel vba, the description are: 1. Code in Outlook for Open Excel file if email subject line match :

Private Sub Items_ItemAdd(ByVal Item As Object)
    If TypeOf Item Is Outlook.MailItem Then
        '// Subject line here
        If InStr(Item.Subject, "Run Dashboard") Then
            Call openExcel
        End If
    End If
End Sub

Once Excel is open and dashboard has run then email has to be sent via excel.

vba and code:

Dim outapp As Object
Dim nmail As Object

Set outapp = CreateObject("Outlook.Application")
Set nmail = outapp.CreateItem(0)
With nmail
    .To = "[email protected]"
    .cc = ""
    .bcc = ""
    .Subject = flname
    .htmlbody = RangetoHTML(Range("A1:" & Split(Cells(, lastcol1).Address, "$")(1) & lastrow1))
    .attachments.Add ActiveWorkbook.FullName
    .display
End With

On Error GoTo 0

Set nmail = Nothing

Set outapp = Nothing

Now I am facing the error on Set outapp = CreateObject("Outlook.Application") This error is only showing if i open excel file through outlook email as mentioned in point 1, if i open file in normal way i.e. without outlook help, then code is running successfully.

Please help in the same.

Thanks in advance

Upvotes: 0

Views: 3340

Answers (1)

Eugene Astafiev
Eugene Astafiev

Reputation: 49455

Why do you need to automate Excel from Outlook and then Outlook from Excel? 

 Set outapp = CreateObject("Outlook.Application")

Instead you may get the running Outlook instance (if any) because only one instance of Outlook can be run simultaneously. See How to automate Outlook from another program and GetObject and CreateObject behavior of Office automation servers for more information.

Try to use the following line of code instead:

 Set nmail = Application.CreateItem(olMailItem)

If you have multiple profiles in Outlook configured most probably you will need to use the Logon method of the Namespace class.

Upvotes: 0

Related Questions