VBA - Send An Email Using Account That I Want
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 22

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

  1. #11
    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

    If I run the above code with .Send, It gives me error "Run-time error '·2147221238 (8004010a)': The item has been moved or deleted."
    And if i run it with .Display it generates all emails but close them as well instantly and in the end only last email is displayed.
    Last edited by fiberboysa; Feb 12th, 2019 at 12:48 AM.

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

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

    It is expected that c.Offset(0, 6).Value gets 1 or 2, please check those numbers in H-column.
    Does the initial code of post 1 is working?
    Vladimir Zakharov

  3. #13
    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

    Yes there are integer values in H column i.e. 1 or 2 which are calculated by a formula.
    Initial code that i posted in 1st post is working but its item value i.e. Set .SendUsingAccount = OutApp.Session.Accounts.Item(1) fixed to "1". I need to pic this value from column H starting from H2 and onwards...

  4. #14
    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

    BTW I modified the code in post 1 to code in post 2 so that it fulfill my needs. But its giving me error "Run-time error '5'. Invalid procedure call or argument." in line Set .SendUsingAccount = OutApp.Session.Accounts.Item(AID).
    Last edited by fiberboysa; Feb 12th, 2019 at 02:43 AM.

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

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

    Thank you for the testing.
    This should work:
    Code:
    Sub MailToDestination()
     
      Dim OutApp As Object, SendTo As String, c As Range, IsCreated As Boolean
     
      On Error Resume Next
      Set OutApp = GetObject(, "Outlook.Application")
      If Err Then
        IsCreated = True
        Set OutApp = CreateObject("Outlook.Application")
      End If
      On Error GoTo exit_
     
      For Each c In Range("B2", Range("B" & Cells.Rows.Count).End(xlUp))
        With OutApp.CreateItem(0)
          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 = OutApp.Session.Accounts.Item(c.Offset(0, 6).Value)
            .Send ' or use .Display
          End If
        End With
      Next
     
      If IsCreated Then
        OutApp.Quit
        Set OutApp = Nothing
      End If
     
    exit_:
      If Err Then MsgBox Err.Description & vbLf & "Account #" & c.Offset(0, 6).Value, vbCritical, "Error #" & Err.Number
     
    End Sub
    Last edited by ZVI; Feb 12th, 2019 at 08:01 AM.
    Vladimir Zakharov

  6. #16
    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

    Yes!!! Thank you this is working perfect...
    Be blessed always.

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

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

    Thank you for the feedback,
    Have a good day!
    Vladimir Zakharov

  8. #18
    New Member
    Join Date
    Aug 2017
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    .dear zvi,
    i iuse below code. i need to send mail from 3 rd accout in my outlook 2016. should i amend below codes, or change completely?

    Sub BABS()
    ' Select the range of cells on the active worksheet.
    ActiveSheet.Range("A1:E9").Select
    ' Show the envelope on the ActiveWorkbook.
    ActiveWorkbook.EnvelopeVisible = True
    ' Set the optional introduction field thats adds
    ' some header text to the email body. It also sets
    ' the To and Subject lines. Finally the message
    ' is sent.
    With ActiveSheet.MailEnvelope
    .Item.To = Range("F8")
    .Item.Subject = Range("G8")
    .Item.Send 'or use .Display
    End With
    'clears every X on top N after sending each mail
    Columns("I:I").Find(What:="*").ClearContents
    End Sub

  9. #19
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,524
    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 turkanet View Post
    .dear zvi,
    i iuse below code. i need to send mail from 3 rd accout in my outlook 2016. should i amend below codes, or change completely?

    ...
    With ActiveSheet.MailEnvelope
    ...
    Hi,

    There is no way to choose account using MailEnvelope, you have to change method completely.
    Try using code of the post #15 or the one (with default signature of the account) that I suggested to you in the another thread.

    Regards
    Last edited by ZVI; Feb 21st, 2019 at 05:48 PM.
    Vladimir Zakharov

  10. #20
    New Member
    Join Date
    Aug 2017
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Dear ZVI,
    i must sent a range ("A1:E9"), not pdf attachment. do you suggest nother way for sending a range with the other mail account?

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
  •