Help improving my very basic EMAIL code 2

[Moss100]

Hello,

I have a form which I want to use for emailing.

From what I have read the easiest way to send mail directly from Access seems to be using Outlook (is this correct)

The code below works with one attachment, but does not attach further attachments.

I would greatly appreciate if someone could help me to rewrite the code (I've basically little idea of what I am doing and am amazed it worked at all!)

I basically have little confidence in my codes robustness and would appreciate it if someone could give me some pointers as to how to improve it (maybe with some error handling etc??). Also is it possible to get a confirmation that the email has actually been sent? (maybe a message box to confirm email sent or email error)

Many many thanks Mark

CURRENT CODE:


Private Sub btn_send_DblClick(Cancel As Integer)

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
.BodyFormat = olFormatRichText
.To = Me.txt_Email_To
If Nz(Me.txt_Email_To.Value, "") <> "" Then .To = Me.txt_Email_To.Value
If Nz(Me.txt_Email_CC.Value, "") <> "" Then .CC = Me.txt_Email_CC.Value
If Nz(Me.txt_Email_BCC.Value, "") <> "" Then .BCC = Me.txt_Email_BCC.Value
If Nz(Me.txt_Email_Attachment_1.Value, "") <> "" Then .Attachments.Add (Me.txt_Email_Attachment_1.Value)
If Nz(Me.txt_Email_Attachment_2.Value, "") <> "" Then .Attachments.Add (Me.txt_Email_Attachment_2.Value)
If Nz(Me.txt_Email_Attachment_3.Value, "") <> "" Then .Attachments.Add (Me.txt_Email_Attachment_3.Value)
If Nz(Me.txt_Email_Attachment_4.Value, "") <> "" Then .Attachments.Add (Me.txt_Email_Attachment_4.Value)
If Nz(Me.txt_Email_Subject.Value, "") <> "" Then .Subject = Me.txt_Email_Subject.Value
If Nz(Me.txt_Email_Message.Value, "") <> "" Then .HTMLBody = Me.txt_Email_Message.Value

.Send
End With
End Sub

 

[Moss100]

Actually to my surprise the above code has indeed delivered the email along with 4 attachments.

I would still be grateful if there are better ways to do this - and also whether a confirm can be received (so the user knows the email is sent before exiting the form).

Many thanks Mark

 

[Moss100]

My Outlook has two mail accounts - is it possible to specify which account is used when the code above is run?

Thanks Mark

 

[1DMF]

Yes, have a look at my FAQ : faq705-7667

Look at the create method and you will see 'SentOnBehalfOfName' property for setting this type of thing.

Here is a list of the mailitem properties...

http://msdn.microsoft.com/en-gb/library/microsoft.office.interop.outlook.mailitem_properties.aspx

You may want to also look at : 'SenderName' / 'SenderEmailAddress'


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[dhookom]

Moss100,
Also consider using code TGML as suggested in other threads.

Private Sub btn_send_DblClick(Cancel As Integer)
 
    Dim appOutLook As Outlook.Application
    Dim MailOutLook As Outlook.MailItem
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)
    With MailOutLook
        .BodyFormat = olFormatRichText
        .To = Me.txt_Email_To
        If Nz(Me.txt_Email_To.Value, "") <> "" Then .To = Me.txt_Email_To.Value
        If Nz(Me.txt_Email_CC.Value, "") <> "" Then .CC = Me.txt_Email_CC.Value
        If Nz(Me.txt_Email_BCC.Value, "") <> "" Then .BCC = Me.txt_Email_BCC.Value
        If Nz(Me.txt_Email_Attachment_1.Value, "") <> "" Then .Attachments.Add (Me.txt_Email_Attachment_1.Value)
        If Nz(Me.txt_Email_Attachment_2.Value, "") <> "" Then .Attachments.Add (Me.txt_Email_Attachment_2.Value)
        If Nz(Me.txt_Email_Attachment_3.Value, "") <> "" Then .Attachments.Add (Me.txt_Email_Attachment_3.Value)
        If Nz(Me.txt_Email_Attachment_4.Value, "") <> "" Then .Attachments.Add (Me.txt_Email_Attachment_4.Value)
        If Nz(Me.txt_Email_Subject.Value, "") <> "" Then .Subject = Me.txt_Email_Subject.Value
        If Nz(Me.txt_Email_Message.Value, "") <> "" Then .HTMLBody = Me.txt_Email_Message.Value

        .Send
    End With
End Sub

I typically prefer to write my "If Then Else End If" on multiple lines but your usage is simple enough that one line works:

If Nz(Me.txt_Email_To.Value, "") <> "" Then 
    .To = Me.txt_Email_To.Value
End If

Duane
Hook'D on Access
MS Access MVP