Access send email attachments - problem when null or empty

[Moss100]

Hello I have a Vba module which works fine for sending emails (through outlook).

I have the option to attach 2 files in my form.

Basically I have textboxs txtattach1 and txtattach2 which hold the file path for each attachment.

If both attachements contain a file path then it works no problem. If however one is empty it rejects it. I have read up and understand the attachments.add property rejects null and empty fields.

How do I get around this?

Also I would like to have up to 4 attachements.

Do I just repeat the attachements.add code for each attachment or is there a better approach to attaching more than one file to the outgoing email?

Thank you kindly. Mark

 

[hermanlaksko]

I have over the years used this and expanded this code perhaps it can be of some use to you.
As you can see org. code was developed by Helen - If you do not know her look her up on the web, she is very informative and worth a visit
'***********************************************************************************************************************
' Navn : SendMailOutlook
' Version : 1.0
' Dato : 11-07-2005 - Last modified 16-11-2005 - 07-04-2008 - 15-03-2013 MB
' Inparam : -
' Outparam : -
' Beskrivelse : Creates email in outlook - 15-03-2013 No ref to outlook (late binding)
' Created by Helen Feddema 11-07-2005 - Last modified 11-07-2005 - 16-11-2005 - 07-04-2008 MB
' TODO : -
'***********************************************************************************************************************
Function SendMailOutlook(ByVal sTo As String, sFrom As String, sSubject As String, sBody As String, _
Optional sCC As Boolean, Optional sBCC As Boolean, Optional sReplyTo As String = "", _
Optional sAttachment As String = "", Optional sAttachment2 As String = "", Optional sAttachmentAlias As String = "", _
Optional sPriority As Integer = 1, Optional sHTML As Boolean, Optional SendNow As Integer) As Boolean
On Error GoTo Fejl
Dim Re As DAO.Recordset, DocName, CCOk As Boolean 'Dim appOutlook As New Outlook.Application, Msg As Outlook.MailItem
Dim AppOutlook As Object, Msg As Object, oAccount As Object
'Create new mail message and send to contacts
Set AppOutlook = CreateObject("Outlook.Application")
Set Msg = AppOutlook.CreateItem(0) 'olMailItem)
With Msg
If sCC Then
.CC = sTo
CCOk = True
ElseIf sBCC Then
.BCC = sTo
CCOk = True
End If
If Not IsBlank(sTo) And Not CCOk Then .To = sTo
.Subject = sSubject
If sHTML Then .HTMLBody = sBody Else .Body = sBody
.Importance = sPriority
'.SenderName = sFrom
.Attachments.Add sAttachment
If Not IsBlank(sAttachment2) Then .Attachments.Add sAttachment2
.SentOnBehalfOfName = sFrom ' oAccount '.SendUsingAccount = oAccount
'Set .SendUsingAccount = oAccount
If SendNow = 1 Then .Send Else .Display 'Show email every time
End With
If Err Then SendMailOutlook = False Else SendMailOutlook = True
ExitHer:
Exit Function
Fejl:
MsgBox Err.Description, , "Your App name"
Resume ExitHer
End Function

 

[strongm]

>Do I just repeat the attachements.add code for each attachment

Yes, you do. Abd just check the attachment name has a value before adding it (you might even want to check it is a valid name as well)

So

If AttachmentName <>"" then Attachments.Add AttachmentName

 

[hermanlaksko]

Ahh.. forgot the IsBlank function - but strongm's <>"" works just the same, however the IsBlank function checks for null, empty, etc. all in one.
'***********************************************************************************************************************
' Navn : IsBlank
' Version : 1.0
' Dato : 06-05-2003
' Inparam : Val
' Outparam : True / False
' Beskrivelse : Chk on en given variant indeholder data - samlet chk for IsNull, IsEmpty, Etc.
' TODO : -
'***********************************************************************************************************************
Function IsBlank(V As Variant) As Boolean
On Error Resume Next
V = "" & V
If Len(V) = 0 Then IsBlank = True
End Function

 

[strongm]

Or you can more efficiently use Nz ..

Function IsBlank(V As Variant) As Boolean
    IsBlank = Nz(V) = ""
End Function

 

[Moss100]

Thank you all for your input - greatly appreciated - all working now

 

[BFroeschlKC]

I've always done this type of check in-line as:

If Len("" & V) > 0 Then AttachFile(V)

 

[Andrzejek]

I would add:

If Len(Trim(V & "")) > 0 Then AttachFile(V)

and eliminate any possibility of one or several Spaces