Send Email via VBA from Excel 2010

[dariuzthepole]

Hi folks,

This problem is driving me crazy. Please help!

I have a macro in Excel and when it finishes, I want to send myself and a colleague a simple text email advising the macro has finished running. The issue I've got is he is using WinXP and Office 2007, whereas I am using Win7 and Office 2010. He can get the following code to run fine and the email works:

Sub SendEmail(EmailAddress As String, Subject As String, Message As String)
     
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    sTo = EmailAddress
    sCC = ""
    sBCC = ""
    sSubject = Subject
    strbody = Message
    With OutMail
        .To = sTo
        .CC = sCC
        .BCC = sBCC
        .Subject = sSubject
        .Body = strbody
        .Send
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

When I try to run the macro from Excel 2010, the email does not get sent. I have added the MS Excel/Office 14.0 Object Library and MS Scripting Runtime references so that they are ticked but I cannot figure out the alterations I need to make the code operational.

N.B. The three variables (EmailAddress, Subject and Message) are defined in a Workbook_Open macro that kicks a series of macros off.

Any help would be greatly appreciated!

 

[kumarapush123]

Hi,
Not sure why this question did not get any response. Please make sure that outlook is running when you run the macro to send email from excel. Else it might stay in the Outbox till you open the MS Outlook.
If the problem still persists, please upload a sample Excel document, so that someone could try to resolve the actual bug.

Reference Article on how to send email from Excel to multiple recipients.

Thanks and Hope this helps.