Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

VBA outlook variable attachment

Good morning everyone, for the below code it is an auto email sender. It works only if there are attachment. Some emails have 1 or more attachments. Thus if the path is blank it will not work. is there anyway to make the pdfadd1 to pdfadd5 ignore adding attachment if the path is empty? Because each email have a variable attachment and some paths is empty. Thank you!

Sub send_mass_email_display_only()

    Dim i As Integer
    Dim name As String, email As String, body As String, subject As String, copy As String, pdfadd1 As String, pdfadd2 As String, pdfadd3 As String, pdfadd4 As String, pdfadd5 As String
    Dim OutApp As Object
    Dim OutMail As Object
    
    
    body = ActiveSheet.TextBoxes("TextBox 1").Text
    
    
    For i = 2 To 3
    'Specific rows
        
        name = Split(Cells(i, 1).Value, " ")(0)
        'name = Cells(i, 1).Value
        email = Cells(i, 2).Value
        subject = Cells(i, 3).Value
        copy = Cells(i, 4).Value
        pdfadd1 = Cells(i, 5).Value
        pdfadd2 = Cells(i, 6).Value
        'pdfadd3 = Cells(i, 7).Value
        'pdfadd4 = Cells(i, 8).Value
        'pdfadd5 = Cells(i, 9).Value
        
        body = Replace(body, "C1", name)
    
        
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        
        With OutMail
             .SentOnBehalfOfName = Cells(7, 17).Value
             .To = email
             .cc = copy
             .subject = subject
             .body = body
             .Attachments.Add (pdfadd1)
             .Attachments.Add (pdfadd2)
             '.Attachments.Add (pdfadd3)
             '.Attachments.Add (pdfadd4)
             '.Attachments.Add (pdfadd5)
             .display
             '.Send
        End With
    
        body = ActiveSheet.TextBoxes("TextBox 1").Text 'reset body text
        
        
        Next i
    
    
    Set OutMail = Nothing
    Set OutApp = Nothing
    
    'MsgBox "Email(s) Sent!"
    
End Sub

>Solution :

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

Here is the relevant part. I just added an If statement to each one to make sure the length is greater than zero.

 .body = body
 If Len(pdfadd1) > 0 Then .Attachments.Add (pdfadd1)
 If Len(pdfadd2) > 0 Then .Attachments.Add (pdfadd2)
 If Len(pdfadd3) > 0 Then .Attachments.Add (pdfadd3)
 If Len(pdfadd4) > 0 Then .Attachments.Add (pdfadd4)
 If Len(pdfadd5) > 0 Then .Attachments.Add (pdfadd5)
 .display
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading