VBA error when cell is empty

[ddeol]

Hi everyone

A right newbie here so please take it easy on me!

I've been using the below code to do a tailored mail merge with specific attachments to my recipients.

There will be varied mailing, where some people will receive 1 attachment, and some with 3 for example.

The problem that I have is that if one of the cells with the attachment file name are empty, it doesn't go onto the next mailing and gives me an error message.

Is there some code I can include so if the 2nd / 3rd attachment cell are empty it moves onto the next person to send an email to?

It only works flawlessly if all 3 attachment cells are filled out at the moment!

I hope this makes sense, however I can try to explain it better!

Thanks in advance

D

Sub SendMail()

Dim objOutlook As Object
Dim objMail As Object
Dim ws As Worksheet


Set objOutlook = CreateObject("Outlook.Application")
Set ws = ActiveSheet


For Each cell In ws.Range("A2:A70")


Set objMail = objOutlook.CreateItem(0)


With objMail
.To = cell.Value
.Subject = cell.Offset(0, 2).Value
.Body = cell.Offset(0, 4).Value
.Attachments.Add cell.Offset(0, 5).Value
.Attachments.Add cell.Offset(0, 6).Value
.Attachments.Add cell.Offset(0, 7).Value
.send
End With


Set objMail = Nothing
Next cell


Set ws = Nothing
Set objOutlook = Nothing


End Sub

 

[ddeol]

Unfortunately I haven't got anywhere since making my post, anyone have any ideas? It's driving me nuts!

 

[Fluff]

Untested, but try

With objMail
.To = cell.Value
.Subject = cell.Offset(0, 2).Value
.Body = cell.Offset(0, 4).Value
If Len(cell.Offset(0, 5).Value) > 0 Then .Attachments.Add cell.Offset(0, 5).Value
If Len(cell.Offset(0, 6).Value) > 0 Then .Attachments.Add cell.Offset(0, 6).Value
If Len(cell.Offset(0, 7).Value) > 0 Then .Attachments.Add cell.Offset(0, 7).Value
.send
End With

 

[bsquad]

ddeol,

I can give you general syntax direction; I am just not sure what you have going with the Add cell; or if you using an old version of MS Office version. You are also missing your 'cell' declaration, I would assume that is a Range since you have 'For each cell in ws.range("A2:A70"). I honestly have not specifically used Outlook in conjunction with Excel - however generally syntax is the same since they are all MS products; but where you have your 'With' statement, from a first look it looks like someone has used the Name Manager for the 'To' and 'Subject' etc...
I can't be for sure, if this would work, but one general way to skip empty cells

    If objMail.attachments.cell.Offset(0, 5).Value <> "" Then
        objMail.attachments.cell.Offset(0, 5).Value
    End If
    If objMail.attachments.cell.Offset(0, 6).Value <> "" Then
        objMail.attachments.cell.Offset(0, 6).Value
    End If
    If objMail.attachments.cell.Offset(0, 7).Value <> "" Then
        objMail.attachments.cell.Offset(0, 7).Value
    End If