Attachments in Outlook

[daveblanch]

Hi,

I have contacts database that is going to be used to send emails (using Outlook) using VBA, occasionally there will be the need to add attachments.

The form has a textbox in which the path to the document to be sent is entered.

All is well whenever an email is sent with an attachment, but when an email is to be sent without an attachment my code falls over stating 'Could not complete the operation. One or more parameter values are not valid"

I don't know how to ignore the .Attachments.Add code if the text box has no value entered. I've tried using Nz but I'm not sure if I'm using it correctly.

Hoping someone can help?

Private Sub btnEmail_Click()

Dim Email As String
Dim Subject As String
Dim Notes As String

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim sAttachment1 As String
Dim sAttachment2 As String
Dim sAttachment3 As String

DoCmd.GoToRecord , , acFirst

Do While Not Recordset.EOF

If Me.Labels = 0 Then

Recordset.MoveNext

Else

Email = Me!Email
Subject = Me!txtSubject
Notes = Me!txtBody
Attachment1 = Nz(Me!txtAtt1)
Attachment1 = Me!txtAtt1
Attachment2 = Me!txtAtt2
Attachment3 = Me!txtAtt3

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail
    .To = Email
    .Subject = Subject
    .Body = Notes & vbNewLine & vbNewLine & "Signature"
    .Attachments.Add Attachment1
    .Attachments.Add Attachment2
    .Attachments.Add Attachment3
    .Send

End With
 
Recordset.MoveNext

End If

Loop

Set objEmail = Nothing

    MsgBox "Your eMails have been sent, please close the close button on the form."

End Sub

Thanks.

 

[pr2-eugin]

Hello Dave, The problem I think might be because you have three attachements, but you are checking only one Attachements also the use of Nz() is a bit off.. Nz() will replace a Null value with a value that you desire..

Nz( [I][B]value_to_check [/B][/I],[I][B] what_if_that_value_is_NULL[/B][/I] )

So based on that, you have to check every attachement, also you can do it at that point without assigning them to variables.. so the coding becomes..

Private Sub btnEmail_Click()

    Dim Email As String
    Dim Subject As String
    Dim Notes As String
    Dim objOutlook As Outlook.Application
    Dim objEmail As Outlook.MailItem

    DoCmd.GoToRecord , , acFirst

    Do While Not Recordset.EOF
        If Me.Labels = 0 Then
            Recordset.MoveNext
        Else
            Email = Me!Email
            Subject = Me!txtSubject
            Notes = Me!txtBody
            Set objOutlook = CreateObject("Outlook.application")
            Set objEmail = objOutlook.CreateItem(olMailItem)

            With objEmail
                .To = Email
                .Subject = Subject
                .Body = Notes & vbNewLine & vbNewLine & "Signature"
                [COLOR=Blue]If Nz(Me!txtAtt1, "")<>"" Then[/COLOR] .Attachments.Add Me!txtAtt1
                [COLOR=Blue]If Nz(Me!txtAtt1, "")<>"" Then[/COLOR] .Attachments.Add Me!txtAtt2
                [COLOR=Blue]If Nz(Me!txtAtt1, "")<>"" Then[/COLOR] .Attachments.Add Me!txtAtt3
                .Send
            End With
            Recordset.MoveNext
        End If
    Loop
    Set objEmail = Nothing
    MsgBox "Your eMails have been sent, please close the close button on the form."
End Sub

See the Blue bits I have added to your code..

 

[daveblanch]

Hi Paul,

Thanks for your reply - the correct use of Nz worked a treat.

I did however get errors when i was attempting to use

[COLOR=#0000ff]If Nz(Me!txtAtt1, "")<>"" Then[/COLOR] .Attachments.Add Me!txtAtt1

with it not liking the use of

.Attachments.Add Me!txtAtt1

I went back to using variables and it works exactly as i needed it too!

Thanks.

And in case anyone is interested:

Option Compare Database
Private Sub btnEmail_Click()
Dim Email As String
Dim Subject As String
Dim Notes As String
Dim sAttachment1 As String
Dim sAttachment2 As String
Dim sAttachment3 As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
DoCmd.GoToRecord , , acFirst
Do While Not Recordset.EOF
If Me.Labels = 0 Then
Recordset.MoveNext
Else
Email = Me!Email
Subject = Me!txtSubject
Notes = Me!txtBody
Attachment1 = Me!txtAtt1
Attachment2 = Me!txtAtt2
Attachment3 = Me!txtAtt3
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)
With objEmail
.To = Email
.Subject = Subject
.Body = Notes & vbNewLine & vbNewLine & "Signature"
If Nz(Me.txtAtt1, "") <> "" Then .Attachments.Add Attachment1
If Nz(Me.txtAtt2, "") <> "" Then .Attachments.Add Attachment2
If Nz(Me.txtAtt3, "") <> "" Then .Attachments.Add Attachment3
.Send
End With
 
Recordset.MoveNext
End If
Loop
Set objEmail = Nothing
MsgBox "Your eMails have been sent, please close the close button on the form."
End Sub