Send an email via OUTLOOK from an EXCEL macro

[peterhw]

I have recently been writing an application to log 'contact details' then modify a word document based on the recorded information - i.e. name, address line 1, city ,post or zip code. I then save the word document an would like to have an option to automatically create the appropriate email message.

The following sort of works but always appears to send from the default email address (anne@ABC.co.uk). SendUsingAccount doesn't appear to make any difference and the .from generates an error.

Any suggestions appreciated.

  <this fails="" -="" doesn="" t="" support="" this="" object="" or="" method=""  .recipients.add="" (mailaddress)="" same="" as="" .to="mailaddress" (i="" think)="" .cc="mailaddress"  .bcc="mailaddress"  .body="===1===" mailcontent="" outapp.session.accounts.item(2)="" vbcrlf="" +=""  .display=""  ="" .htmlbody="strbody" &="">
Sub Send_Email_Using_VBA(mailaddress As String, mailcontent As String, mailsubject As String)
'==================================================================================
Dim mail_account_nos As Integer
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    On Error GoTo debugs
    mail_account_nos = 2
    With OutMail
        .Subject = mailsubject
        '.From = "panda@ABC.co.uk"     '<<<<<<<<<<<<<<<<this fails - "Object doesn't support this object or method"
        .To = mailaddress
        .Recipients.Add (mailaddress)   '   same as .To (I think)
        .CC = mailaddress
        .BCC = mailaddress
        .Body = "===1===" + vbCrLf + mailcontent + vbCrLf + OutApp.Session.Accounts.Item(mail_account_nos) + vbCrLf + "===1==="
        .Display                                            'OutApp.Session.Accounts.Item(mail_account_nos) change 2 to 1 and it changes this output but not the account sent from
        '.HTMLBody = strbody & "<br><br>" & Signature
        'You can add files also like this
        '.Attachments.Add ("C:\test.txt")
        .SendUsingAccount = OutApp.Session.Accounts.Item(mail_account_nos)  '  have 2 accounts "panda@ABC.co.uk" and "anne@ABC.co.uk" always send from anne - the default
        '.send

    End With
debugs:
    If Err.Description <> "" Then MsgBox Err.Description
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

</this>

 

[peterhw]

Not sure what I have done but the code is not what I pasted.
Try again

Sub Send_Email_Using_VBA(mailaddress As String, mailcontent As String, mailsubject As String)
'==================================================================================
Dim mail_account_nos As Integer
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    On Error GoTo debugs
    mail_account_nos = 2
    With OutMail
        .Subject = mailsubject
        '.From = "panda@ABC.co.uk"     '<<<<<<<<<<<<<<<<this fails - "Object doesn't support this object or method"
        .To = mailaddress
        .Recipients.Add (mailaddress)   '   same as .To (I think)
        .CC = mailaddress
        .BCC = mailaddress
        .Body = "===1===" + vbCrLf + mailcontent + vbCrLf + OutApp.Session.Accounts.Item(mail_account_nos) + vbCrLf + "===1==="
        .Display                                            'OutApp.Session.Accounts.Item(mail_account_nos) change 2 to 1 and it changes this output but not the account sent from
        '.HTMLBody = strbody & "<br><br>" & Signature
        'You can add files also like this
        '.Attachments.Add ("C:\test.txt")
        .SendUsingAccount = OutApp.Session.Accounts.Item(mail_account_nos)  '  have 2 accounts "panda@ABC.co.uk" and "anne@ABC.co.uk" always send from anne - the default
        '.send

    End With
debugs:
    If Err.Description <> "" Then MsgBox Err.Description
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[peterhw]

Sorry - but just don't seem to be able to include CODE properly


Sub Send_Email_Using_VBA(mailaddress As String, mailcontent As String, mailsubject As String)
'==================================================================================
Dim mail_account_nos As Integer
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
On Error GoTo debugs
mail_account_nos = 2
With OutMail
.Subject = mailsubject
'.From = "panda@ABC.co.uk" '<<<<<<<<<<<<<<<<this fails - "Object doesn't support this object or method"
.To = mailaddress
.Recipients.Add (mailaddress) ' same as .To (I think)
.CC = mailaddress
.BCC = mailaddress
.Body = "===1===" + vbCrLf + mailcontent + vbCrLf + OutApp.Session.Accounts.Item(mail_account_nos) + vbCrLf + "===1==="
.Display 'OutApp.Session.Accounts.Item(mail_account_nos) change 2 to 1 and it changes this output but not the account sent from
'.HTMLBody = strbody & "<br><br>" & Signature
'You can add files also like this
'.Attachments.Add ("C:\test.txt")
.SendUsingAccount = OutApp.Session.Accounts.Item(mail_account_nos) ' have 2 accounts "panda@ABC.co.uk" and "anne@ABC.co.uk" always send from anne - the default
'.send

End With
debugs:
If Err.Description <> "" Then MsgBox Err.Description
Set OutMail = Nothing
Set OutApp = Nothing
End Sub