VBA - Send An Email Using Account That I Want

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,676
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:
Rich (BB 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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

turkanet

New Member
Joined
Aug 20, 2017
Messages
16
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
......
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,676
... 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):
Rich (BB 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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,301
Messages
5,486,045
Members
407,529
Latest member
netojose

This Week's Hot Topics

Top