Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: VBA - Send An Email Using Account That I Want

  1. #1
    Board Regular
    Join Date
    Apr 2012
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile VBA - Send An Email Using Account That I Want

    Hi Dear All,
    I found following code to send email from Excel using a specific account. But the thing is I am using two different accounts in Outlook and want to send some emails using the account that I specify in a cell in the excel sheet. Can anyone please can make this 1 in Set .SendUsingAccount = OutApp.Session.Accounts.Item(1) brackets a variable linked to excel sheet instead of a constant so I can use it? I am using Excel 2016.

    Code:
    Sub Mail_small_Text_Change_Account()'Only working in Office 2007-2016
    'Don't forget to set a reference to Outlook in the VBA editor
        Dim OutApp As Outlook.Application
        Dim OutMail As Outlook.MailItem
        Dim strbody As String
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(olMailItem)
    
        strbody = "Hi there" & vbNewLine & vbNewLine & _
                  "This is line 1" & vbNewLine & _
                  "This is line 2" & vbNewLine & _
                  "This is line 3" & vbNewLine & _
                  "This is line 4"
    
        On Error Resume Next
        With OutMail
            .To = "ron@debruin.nl"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .Body = strbody
    
            'SendUsingAccount is new in Office 2007
            'Change Item(1)to the account number that you want to use
            Set .SendUsingAccount = OutApp.Session.Accounts.Item(1)
    
            .Send   'or use .Display
        End With
        On Error GoTo 0
    
        Set OutMail = Nothing
        Set OutApp = Nothing End Sub
    Last edited by fiberboysa; Jan 22nd, 2019 at 05:59 AM.

  2. #2
    Board Regular
    Join Date
    Apr 2012
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Send An Email Using Account That I Want

    Please help so that i can pick that (1) in Set .SendUsingAccount = OutApp.Session.Accounts.Item(1) from a cell in a row. I have tried the following code but its not working giving me Run-tim error '5' Invalid procedure call or argument error...

    Code:
    Sub MailToDestination()    Dim SendTo As String
        Dim ToMSg As String
        Dim ToSubject As String
        Dim CC As String
        Dim AID As Integer
        For Each c In Range(Range("B2"), Range("B" & Cells.Rows.Count).End(xlUp))
            SendTo = c
            If Not (IsError(c.Offset(0, 1))) Then
                If c.Offset(0, 0) <> "" Then SendTo = c.Offset(0, 0)
            End If
            If SendTo <>  Then
                ToSubject = c.Offset(0, 2)
                ToMSg = c.Offset(0, 3)
                CC = c.Offset(0, 1)
                AID = c.Offset(0, 6)
                Send_Mail SendTo, ToSubject, ToMSg, CC
            End If
        Next
    End Sub
    Sub Send_Mail(SendTo As String, ToSubject, ToMSg As String, CC As String)
        Dim OutApp As Object
        Dim OutMail As Object
        Dim StrSignature As String
        Dim sPath As String
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
     
        With OutMail
            .To = SendTo
            .CC = CC
            .BCC = ""
            .Subject = ToSubject
            .Body = ToMSg & vbCrLf & vbCrLf & "Best Regards,"
            Set .SendUsingAccount = OutApp.Session.Accounts.Item(AID)
            .Display  ' or just put .Send to directly send the mail instead of display
        End With
     
        Set OutMail = Nothing
        Set OutApp = Nothing
    End Sub

  3. #3
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,639
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA - Send An Email Using Account That I Want

    Hi,
    Try this code:
    Code:
    Sub MailToDestination()
      Dim SendTo As String
      Dim c As Range
      With CreateObject("Outlook.Application")
        For Each c In Range("B2", Range("B" & Cells.Rows.Count).End(xlUp))
          SendTo = Trim(c.Value)
          If SendTo <> "" Then
            .To = SendTo
            .CC = c.Offset(0, 1).Value
            .Subject = c.Offset(0, 2).ValueV
            .Body = c.Offset(0, 3).Value
            Set .SendUsingAccount = .Session.Accounts.Item(c.Offset(0, 6).Value)
            .Send ' or use .Display
          End If
        Next
      End With
    End Sub
    Last edited by ZVI; Feb 11th, 2019 at 05:14 AM.
    Vladimir Zakharov

  4. #4
    Board Regular
    Join Date
    Apr 2012
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Send An Email Using Account That I Want

    Nope It did not worked. It game me error "Run-time error 424. Object Required"
    I tried the following code...

    Code:
    Sub MailToDestination()    Dim SendTo As String
        Dim ToMSg As String
        Dim ToSubject As String
        Dim CC As String
        Dim AID As Integer
        For Each c In Range(Range("B2"), Range("B" & Cells.Rows.Count).End(xlUp))
            SendTo = c
            If Not (IsError(c.Offset(0, 1))) Then
                If c.Offset(0, 0) <> "" Then SendTo = c.Offset(0, 0)
            End If
            If SendTo <> “” Then
                ToSubject = c.Offset(0, 2)
                ToMSg = c.Offset(0, 3)
                CC = c.Offset(0, 1)
                AID = c.Offset(0, 6)
                Send_Mail SendTo, ToSubject, ToMSg, CC
            End If
        Next
    End Sub
    Sub Send_Mail(SendTo As String, ToSubject, ToMSg As String, CC As String)
        Dim OutApp As Object
        Dim OutMail As Object
        Dim StrSignature As String
        Dim sPath As String
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
     
        With OutMail
            .To = SendTo
            .CC = CC
            .BCC = ""
            .Subject = ToSubject
            .Body = ToMSg & vbCrLf & vbCrLf & "Best Regards,"
            Set .SendUsingAccount = OutApp.Session.Accounts.Item(c.Offset(0, 6).Value)
            .Display  ' or just put .Send to directly send the mail instead of display
        End With
     
        Set OutMail = Nothing
        Set OutApp = Nothing
    End Sub

  5. #5
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,639
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA - Send An Email Using Account That I Want

    Quote Originally Posted by fiberboysa View Post
    Nope It did not worked. It game me error "Run-time error 424. Object Required"
    What code line has been yellowed by a debugger?
    Vladimir Zakharov

  6. #6
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,639
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA - Send An Email Using Account That I Want

    I see now - there is my typo in this line .Subject = c.Offset(0, 2).ValueV
    Should be
    .Subject = c.Offset(0, 2).Value
    Vladimir Zakharov

  7. #7
    Board Regular
    Join Date
    Apr 2012
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Send An Email Using Account That I Want

    Quote Originally Posted by ZVI View Post
    What code line has been yellowed by a debugger?
    Same line which I need to be fixed i.e.
    Code:
    Set .SendUsingAccount = OutApp.Session.Accounts.Item(c.Offset(0, 6).Value)

  8. #8
    Board Regular
    Join Date
    Apr 2012
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Send An Email Using Account That I Want

    Quote Originally Posted by ZVI View Post
    I see now - there is my typo in this line .Subject = c.Offset(0, 2).ValueV
    Should be
    .Subject = c.Offset(0, 2).Value
    Above line is OK because I Just changed the line Set .SendUsingAccount = OutApp.Session.Accounts.Item(AID) to Set .SendUsingAccount = OutApp.Session.Accounts.Item(c.Offset(0, 6).Value)

  9. #9
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,639
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA - Send An Email Using Account That I Want

    Quote Originally Posted by fiberboysa View Post
    Above line is OK because I Just changed the line Set .SendUsingAccount = OutApp.Session.Accounts.Item(AID) to Set .SendUsingAccount = OutApp.Session.Accounts.Item(c.Offset(0, 6).Value)
    There is no such a line in my code, variable OutApp is not used in it.
    Please copy/paste all the suggested code to a new VBA module and run it.
    Last edited by ZVI; Feb 11th, 2019 at 07:34 AM.
    Vladimir Zakharov

  10. #10
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,639
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA - Send An Email Using Account That I Want

    My bad, should be With CreateObject("Outlook.Application").CreateItem(0)
    Code:
    Sub MailToDestination()
      Dim SendTo As String
      Dim c As Range
      With CreateObject("Outlook.Application").CreateItem(0)
        For Each c In Range("B2", Range("B" & Cells.Rows.Count).End(xlUp))
          SendTo = Trim(c.Value)
          If SendTo <> "" Then
            .To = SendTo
            .CC = c.Offset(0, 1).Value
            .Subject = c.Offset(0, 2).Value
            .Body = c.Offset(0, 3).Value
            Set .SendUsingAccount = .Session.Accounts.Item(c.Offset(0, 6).Value)
            .Send ' or use .Display
          End If
        Next
      End With
    End Sub
    Last edited by ZVI; Feb 11th, 2019 at 07:48 AM.
    Vladimir Zakharov

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •