Page 3 of 3 FirstFirst 123
Results 21 to 24 of 24

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

  1. #21
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,626
    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
    i must sent a range ("A1:E9"), not pdf attachment. do you suggest nother way for sending a range with the other mail account?
    Try:
    Code:
    Sub SendRange_FromAccountWithItsSignatiure()
    ' ZVI:2019-02-22 https://www.mrexcel.com/forum/general-excel-discussion-other-questions/1084649-vba-send-email-using-account-i-want.html
     
      ' --> User settings, change to suit
      Const MyRange = "A1:E9"            ' Range to be copied into a body of email
      Const Account = 3                  ' Index or Name of the account to send from
      ' <-- End of the settings
     
      Dim IsCreated As Boolean
      Dim OutlApp As Object, sBody As String
     
      ' Create two lines of the body's text
      sBody = "Dear Customer," & vbLf _
            & "Your data is in the below table"
     
      ' Use the already open Outlook if possible
      On Error Resume Next
      Set OutlApp = GetObject(, "Outlook.Application")
      If Err Then
        Set OutlApp = CreateObject("Outlook.Application")
        IsCreated = True
      End If
      On Error GoTo 0
     
      ' Prepare email
      With OutlApp.CreateItem(0)
     
        ' Set HTML format
        .BodyFormat = 2
       
        ' Set the required account by const Account
        Set .SendUsingAccount = OutlApp.Session.Accounts.Item(Account)
      
        ' Prepare fields of email
        .Subject = "Report on " & Now
        .To = ""   ' <-- Put email of the recipient here
     
        ' Copy MyRange in Excel
        Application.CutCopyMode = False
        Range(MyRange).Copy
       
        ' Build the body of email
        With .GetInspector.WordEditor.Content
          .InsertBefore sBody
          With .Paragraphs(2).Range
            .Collapse 0
            .Paste
            .Paragraphs.Add
          End With
        End With
       
        ' Disable copy mode of Excel
        Application.CutCopyMode = False
     
        ' Display & send the created e-mail
        .Display
        '.Send ' <-- Uncomment this line to send
     
      End With
     
     
      ' Quit Outlook in case it was created via this code
      If IsCreated Then OutlApp.Quit
     
      ' Release the memory of the object variable
      Set OutlApp = Nothing
     
    End Sub
    Vladimir Zakharov

  2. #22
    New Member
    Join Date
    Aug 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Dear Vladimir, thank you thousands of times.

  3. #23
    New Member
    Join Date
    Aug 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Dear Vladimir,
    my macro starts like below. i added "Const Account = 2 'select account to send" line, but it again sends from default account. how can i make it send from account 2? thank you in advance

    Sub Send_PDF_CUS_Draft()
    ' --> User settings, change to suit
    Const IsDisplay As Boolean = True ' Change to False for .Send instead of .Display
    Const IsSilent As Boolean = False ' Change to True to show Send status
    Const FontName = "Candara" ' Font name of the email body
    Const FontSize = 11 ' Font size of the email body
    Const Account = 2 'select account to send
    ' <-- End of the settings
    Dim IsCreated As Boolean
    Dim OutlApp As Object
    Dim char As Variant
    Dim PdfFile As String, HtmlFont As String, HtmlBody As String, HtmlSignature As String
    ......

  4. #24
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,626
    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
    ... i added "Const Account = 2 'select account to send" line, but it again sends from default account. how can i make it send from account 2?
    Hi,
    The Account is just a constant.
    Choosing of the account provides this code line (see post #21):
    Code:
        ' Set the required account by const Account
        Set .SendUsingAccount = OutlApp.Session.Accounts.Item(Account)
    Check that this code line is present in your code, or alternatively post full the code.
    Regards
    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
  •