How to change with VBA the sender email and insert the signature for outlook email triggering?

Question

I have to send a report where I need to insert a table in the body of the email, but I need to send another email (MBX) of the outlook and not of my staff, I’m not getting it, I tried with . from but it still doesn’t work. I also need to enter the signature at the end of the email and I’m not getting it either. Someone can help me?

Sub Envia_Email_Tabela()


Application.DisplayAlerts = False


Sheets("Tabela").Select

Dim email_envio As Variant

email_envio = ThisWorkbook.Sheets("E-MAIL").Cells(3, 2) 'e-mail para qual será enviado

Range("A7").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

ActiveWorkbook.EnvelopeVisible = True

With ActiveSheet.MailEnvelope
    '.Item.From = ThisWorkbook.Sheets("E-MAIL").Cells(1, 9) 
    .Introduction = ThisWorkbook.Sheets("E-MAIL").Cells(3, 4)
    .Item.To = ThisWorkbook.Sheets("E-MAIL").Cells(3, 2)
    .Item.CC = ThisWorkbook.Sheets("E-MAIL").Cells(3, 3)
    .Item.Subject = ThisWorkbook.Sheets("E-MAIL").Cells(3, 5) & "  " & ThisWorkbook.Sheets("E-MAIL").Cells(1, 3) & " " & ThisWorkbook.Sheets("E-MAIL").Cells(3, 6) 'Assunto do e-mail
    .Item.Send


End With

End Sub

Answer 1

Send from desired Outlook user

For this, the .Sendusingaccount should be used and as input parameter an account of your Outlook.

In this example the account is being entered manually or fixed with OApp.Session.Accounts.item(2), but an example of loop in Accounts = OApp.Session.Accounts is showing that there are other ways to accomplish this, including by creating a Userform to select the desired account.

In some versions the .SendUsingAccount is an object and in others not, therefore, depending on the version is Set .SendUsingAccount = OApp.Session.Accounts.item(2) and in other .SendUsingAccount = OApp.Session.Accounts.item(2)

Reference

The Outlook reference must be enabled in Tools -> References...

And enable the item "Microsoft Outlook 16.0 Object Library"

Code

Dim OApp As Object, OMail As Object, Accounts As Object
Set OApp = CreateObject("Outlook.Application")
    
Dim oAccount As Outlook.Account
Dim i As Long

Set Accounts = OApp.Session.Accounts

'Verificar as contas do seu Outlook
For Each oAccount In Accounts
    Debug.Print vbCr & "oAccount: " & oAccount
Next
For i = 1 To OApp.Session.Accounts.Count
    Debug.Print OApp.Session.Accounts.item(i)
Next i

Set OMail = OApp.CreateItem(0)
With OMail
    .Display
End With

'https://www.rondebruin.nl/win/s1/outlook/bmail2.htm
HtmlContent = RangetoHTML(Selection)

With OMail
    'Alterar o número em OApp.Session.Accounts.item(2) para a conta desejada
    .SentOnBehalfOfName = OApp.Session.Accounts.item(2)
    Set .SendUsingAccount = OApp.Session.Accounts.item(2) 'Em algumas versões não é um objeto e o Set deve ser retirado
    Introducao = "Prezado, bom dia!.<br>Seguem os dados:<br>" 'Texto Intro Corpo do e-mail
    Signature = OMail.HTMLbody
    .To = email_envio
    .Cc = "[email protected]"               'Quem será copiado
    .Subject = "Teste "                      'Assunto do e-mail
    '.Attachments.Add 'Anexos
    .HTMLbody = Introducao & "<br>" & HtmlContent & "<br>" & Signature
    .Send
End With
Set OMail = Nothing
Set OApp = Nothing

The function RangetoHTML() of Ron de Bruin should be added to the code. For more details see this answer

Another way is sending by CDO, for more information see Sending mail from Excel with CDO, which will send by SMTP and then it is possible to send by any email, not requiring a configured Outlook (if not an Office Outlook user).