mardi 8 décembre 2015

VBA: Skipping lines of code

First, thanks for reviewing my question. I realize there are several who get angry when simple questions are asked, but the answers I'm finding are so complicated a beginner has a very difficult time making it work. I'm wanting to keep this as simple as possible.

I am sending emails out to approximately 150 individuals, each email may have between 1 and 3 attachments.

I can send emails out just fine with one attachment...getting multiple attachments is difficult.

Lets say the attachment file path is located in A1 through C1.

How can I Perform.

If A1 is empty, go to Send, if not, attach file If B1 is empty, go to Send, if not, attach file If C1 is empty, go to Send, if not, attach file

Send:

This is the code I have currently: I realize my ranges are different than what I posted above. The following script works...its just for the one attachment however.

Set rngEntries = ActiveSheet.Range("b5:b172")

For Each rngEntry In rngEntries
    Set objMail = objOutlook.CreateItem(0)
    With objMail
        .To = rngEntry.Offset(0, 11).Value
        .Subject = rngEntry.Offset(0, 8).Value
        .Body = rngEntry.Offset(0, 10).Value
        .Attachments.Add rngEntry.Offset(0, 9).Value
        .send
    End With
Next rngEntry

What I want would look a little like this....

Set rngEntries = ActiveSheet.Range("b5:b172")

For Each rngEntry In rngEntries
    Set objMail = objOutlook.CreateItem(0)
    With objMail
        .To = rngEntry.Offset(0, 11).Value
        .Subject = rngEntry.Offset(0, 8).Value
        .Body = rngEntry.Offset(0, 10).Value

If rngEntry.Offset(0, 1) is empty, goto Send

        .Attachments.Add rngEntry.Offset(0, 1).Value

If rngEntry.Offset(0, 2) is empty, goto Send

        .Attachments.Add rngEntry.Offset(0, 2).Value

If rngEntry.Offset(0, 3) is empty, goto Send

        .Attachments.Add rngEntry.Offset(0, 3).Value

Send: 
        .send
    End With

Next rngEntry

Aucun commentaire:

Enregistrer un commentaire