Send email from Excel in Exchange environment

Question

I have a userform that helps different users fill in data into the spreadsheet. As soon as the data is inserted it should also be sent by email to a few recipients, depending on the options filled in the form.

This happens within a corporate environment using Exchange. I would create a new email account for this file to be able to send the email as an entity and not use the user's email account.

Is this possible? How? I have googled for it and all I can find is how to create a mail message that the user sends from his account.

It's how its set up. The spreadsheet is where the data will sit, to be analyzed and is inserted by many different users (sources) over time. We want an email to be sent to a few users (targets) announcing that the new data has come in. — greye, May 25 '11 at 10:34
@Jean-François Corbett: I'm sorry, I didn't mean to be condescending - sometimes with such questions it turns out that the OP asks "how to do X using Y", with Y being a tool that seems most likely to him, while it is not actually relevant and he just wants to "do X". This is not the case here, but it is IMHO better to ask and remove the doubt. — Piskvor left the building, May 26 '11 at 08:09
@Piskvor: How do you suggest the OP "do X" in this case? Can you suggest a better Y than what the OP put forth? I've had to solve this exact same problem before, and if there are alternative Y's, then I'd like to know. — Jean-François Corbett, May 26 '11 at 08:42

score 4 · Answer 1 · answered May 26 '11 at 07:40

I've used the code below (source) to send e-mails from Excel-VBA. I've only tested it with my own e-mail account, but I assume you could have it send mail from a different account (msgOne.from = ...), as long as the user has permission to send from that account on the Exchange server.

Dim cdoConfig
Dim msgOne

Set cdoConfig = CreateObject("CDO.Configuration")
With cdoConfig.Fields
    .Item(cdoSendUsingMethod) = cdoSendUsingPort
    .Item(cdoSMTPServerPort) = 25 '465 ' (your port number) usually is 25
    .Item(cdoSMTPServer) = "smtp.mysmtpserver.com" ' your SMTP server goes here
    '.Item(cdoSendUserName) = "My Username"
    '.Item(cdoSendPassword) = "myPassword"
    .Update
End With

Set msgOne = CreateObject("CDO.Message")
Set msgOne.Configuration = cdoConfig
msgOne.To = "someone@somewhere.com"
msgOne.from = "me@here.com"
msgOne.subject = "Test CDO"
msgOne.TextBody = "It works just fine."
msgOne.Send

Unfortunately, I can't test this hypothesis at this time, as I'm only set up to send from one account. Let me know how it works out!

score 2 · Answer 2 · edited Feb 04 '16 at 21:17

If the excel application is running on a machine with outlook, you can something along the following.

Function SendEmailWithOutlook(er As emailRecord, 
           recipients As String, 
           cc As String, 
           subject As String, 
           body As String, 
           attachmentPath As String) As Boolean
    Dim errorMsg As String
    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error GoTo errHandle
    If (er.useTestEmail = True) Then
        recipients = er.emailTest
        cc = er.emailTest
    End If
    With OutMail
        If er.emailFrom <> "" Then
            .sentOnBehalfOfName = er.emailFrom
        End If
        .To = recipients
        .cc = cc
        .bcc = er.emailBcc
        .subject = subject
        .htmlBody = body
        If attachmentPath <> "" Then
            .Attachments.Add attachmentPath
        End If
        .Send   'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
    SendEmailWithOutlook = True
    Exit Function
errHandle:
    errorMsg = "Error sending mail via outlook: " & Err.Description & vbCrLf
    errorMsg = errorMsg & "OnBehalfOf:" & er.emailFrom & vbCrLf
    errorMsg = errorMsg & "Recipients: " & recipients & vbCrLf
    errorMsg = errorMsg & "CC: " & cc & vbCrLf
    errorMsg = errorMsg & "BCC: " & er.emailBcc
    MsgBox errorMsg
    SendEmailWithOutlook = False
End Function

Add a reference to Microsoft Outlook 14.0 Object Library

score 0 · Answer 3 · edited Mar 17 '16 at 15:01

0

Why not use the Outlook Object Model?

You can give the current user the right to send on behalf of the specified user, then set MailItem.SentOnBehalfOfName and MailItem.ReplyRecipients (if necessary) properties before callign MailItem.Send.

edited Mar 17 '16 at 15:01

Jean-François Corbett

37,420
30
139
188

answered May 26 '11 at 20:30

Dmitry Streblechenko

62,942
4
53
78

This would a great answer if it had a bit more detail on the coding parts – TylerH Feb 04 '16 at 19:27
Which part are you having problems with? – Dmitry Streblechenko Feb 04 '16 at 20:08
1

I don't have a related issue; I just landed on this page and saw the answer. Where the others provide (a ton of) code, this one only mentions some objects to use. While that *is* helpful in that it points the user in the right direction, I think adding a minimal example of such objects in use would make this possibly the best answer on the question. I figured it might be worth commenting as much. :-) – TylerH Feb 04 '16 at 20:33

Send email from Excel in Exchange environment

3 Answers3

Linked