Send E-mail using Other account by outlook.


Sometime there are more than one account are configured in outlook and when we use VBA to send Emails, it use the default account. but in case you have to send emails through other account , you can use this code.
the bold lines are telling outlook to use another account to send emails.
Sub SendEmail()
    Dim objOutlook As Object
    Dim objItem As Object
    Dim rngRange As Range
    Dim rngRow As Range
    Dim objOutlookAccount As Object
    Dim lngCounter As Long
    Set rngRange = Intersect(Sheet1.Range(“A1”).CurrentRegion.Offset(1), Sheet1.Range(“A1”).CurrentRegion)
    Set objOutlook = CreateObject(“Outlook.Application”)
    lngCounter = 0
    If Not rngRange Is Nothing Then
        For Each rngRow In rngRange.Rows
            Set objOutlookAccount = GetOutlookAccount(objOutlook, rngRow.Cells(1).Value)
            If Not objOutlookAccount Is Nothing Then
                Set objItem = objOutlook.CreateItem(0)
                With objItem
                    Set .SendUsingAccount = objOutlookAccount
                     .to = rngRow.Cells(2).Value
                    .Subject = rngRow.Cells(3).Value
                    .body = rngRow.Cells(4).Value
                    .send
                    lngCounter = lngCounter + 1
                End With
            End If
        Next rngRow
    End If
    If lngCounter > 0 Then
        MsgBox lngCounter & ” Mail sent”, vbInformation
    Else
        MsgBox “No mail sent,please check if you have these account configure”, vbCritical
    End If
End Sub
Function GetOutlookAccount(objOutlook As Object, strEmailId As String) As Object
   Dim objOAccount As Object
    For Each objOAccount In objOutlook.Session.Accounts
        If objOAccount.DisplayName = strEmailId Then
            Set GetOutlookAccount = objOAccount
            Exit For
        End If
    Next objOAccount
End Function
to use this code , please have your data in this format. this code will send all email on one click.
SendEmailUsingThisAccount Email address(es) Subject Body Text
Account1@gmail.com Email@gmail.com Subject1 BodyText1
Account2@gmail.com Email@gmail.com Subject1 BodyText1
Account1@gmail.com Email@gmail.com Subject1 BodyText1
Thanks for reading.

5 Comments Add yours

  1. Meni Porat says:

    Hi Rajan,

    As an experienced VBA developer you know you should have an “On error” statement in your code.

    Best regards,
    Meni Porat

  2. Hi Meni

    you cant often find “On Error” statement in my codes, i don’t call it error handling to just use only “On error” statement.rather than i use lots of if statement to handle errors in my codes,

    i can think you might be getting error in this code for three reason

    1) . Syntax Error ( you can remove Quote sign and put them back)
    2). you don’t have outlook installed on your PC (rare chance so not including Application check code)
    3) . You don’t have the specified account configure in your outlook

    1. Mark says:

      All of which could be handled by On Error sending to an error handler routine that could be coded to likely tell the user exactly what type of error was encountered so if Outlook isn’t installed, the code would tell the user, and same thing if it’s an encountered syntax error or an account not configured in Outlook.
      I don’t do VBA with Outlook, but have most of my Excel macros taking advantage of On Error to tell me exactly what problem was encountered if it fails for some reason.

  3. Thomas Ellebæk says:

    The SentOnBehalfOf property is also and option, but a different purpose.

    Regarding error handling, you don’t need error handler in all functions or routines, it depend on your application and error handling design. Therefore I think it’s a good idea to introduce new functionality without error handler, as above example.

    Thanks for sharing.

  4. Theo Grivel says:

    Great solution. I normally let my people close Outlook and start it again using the desired account and then start the program that sends the e-mails.
    With this code we can mak it a little bit more efficient.
    Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s