Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

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

  1. #11
    Board Regular fiberboysa's Avatar
    Join Date
    Apr 2012
    Posts
    57
    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 01:48 AM.

  2. #12
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,464
    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
    Microsoft MVP Office Apps & Services

  3. #13
    Board Regular fiberboysa's Avatar
    Join Date
    Apr 2012
    Posts
    57
    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 fiberboysa's Avatar
    Join Date
    Apr 2012
    Posts
    57
    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 03:43 AM.

  5. #15
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,464
    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 09:01 AM.
    Vladimir Zakharov
    Microsoft MVP Office Apps & Services

  6. #16
    Board Regular fiberboysa's Avatar
    Join Date
    Apr 2012
    Posts
    57
    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,464
    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
    Microsoft MVP Office Apps & Services

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
  •