VBA Macro to Attach multiple PDFs and send email

M

mcafeecj

New Member
Joined
Dec 17, 2015
Messages
2
I am fairly new to writing codes and built the code below using others' codes, and modified to suit my needs.

I send invoices everyday (which are pdfs). Sometimes I need to send emails with multiple attachments (based on if a customer has two or more invoices that need to be sent out for the day).

I have formulas built in excel to pull out invoice number, company name and email address.

The only manual thing I do is open the folder where all my invoices (pdfs) are stored and "copy as path" into excel.

The code below is based on the following:
column A I have the email address
column B I have the subject
column C I have the body
column D I have the attachment

I sort column G (which has the company's name) so that I can attach multiple invoices. The IF statement in the code below recognizes that if an email address appears twice in a row (in column A) we need to send one email and attach both attachments in column D. If an email address appears three times in a row, the code knows to send one email and attach 3 attachments from column D.

The problem that I am running into is that the code is very long, and I'm not sure how to shorten it. Currently it is built to be able to attach 41 attachments per email (if there are any more attachments the code is too long).

Also at the end of the code you will see I call another Macro in (MoveAndHighlightEmailedCells). What this Macro does is moves the top row/s of data to the bottom and highlights them to show that I have sent the email. It loops until it reaches "Email Address/es Here" (which is an IFERROR formula that says if VLOOKUP results in an error, then return "Email Address/es Here".

Here is the code:

Sub SendEmail()
Dim olApp As Outlook.Application
Dim olAtt As Outlook.Attachments
Dim olMsg As Outlook.MailItem


Set olApp = CreateObject("Outlook.Application")
Set olMsg = olApp.CreateItem(olMailItem)
Set olAtt = olMsg.Attachments


If (Range("A3") = Range("A50")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
olAtt.Add (Range("D27"))
olAtt.Add (Range("D28"))
olAtt.Add (Range("D29"))
olAtt.Add (Range("D30"))
olAtt.Add (Range("D31"))
olAtt.Add (Range("D32"))
olAtt.Add (Range("D33"))
olAtt.Add (Range("D34"))
olAtt.Add (Range("D35"))
olAtt.Add (Range("D36"))
olAtt.Add (Range("D37"))
olAtt.Add (Range("D38"))
olAtt.Add (Range("D39"))
olAtt.Add (Range("D40"))
olAtt.Add (Range("D41"))
olAtt.Add (Range("D42"))
olAtt.Add (Range("D43"))
olAtt.Add (Range("D44"))
olAtt.Add (Range("D45"))
olAtt.Add (Range("D46"))
olAtt.Add (Range("D47"))
olAtt.Add (Range("D48"))
olAtt.Add (Range("D49"))
olAtt.Add (Range("D50"))
ElseIf (Range("A3") = Range("A49")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
olAtt.Add (Range("D27"))
olAtt.Add (Range("D28"))
olAtt.Add (Range("D29"))
olAtt.Add (Range("D30"))
olAtt.Add (Range("D31"))
olAtt.Add (Range("D32"))
olAtt.Add (Range("D33"))
olAtt.Add (Range("D34"))
olAtt.Add (Range("D35"))
olAtt.Add (Range("D36"))
olAtt.Add (Range("D37"))
olAtt.Add (Range("D38"))
olAtt.Add (Range("D39"))
olAtt.Add (Range("D40"))
olAtt.Add (Range("D41"))
olAtt.Add (Range("D42"))
olAtt.Add (Range("D43"))
olAtt.Add (Range("D44"))
olAtt.Add (Range("D45"))
olAtt.Add (Range("D46"))
olAtt.Add (Range("D47"))
olAtt.Add (Range("D48"))
olAtt.Add (Range("D49"))
ElseIf (Range("A3") = Range("A48")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
olAtt.Add (Range("D27"))
olAtt.Add (Range("D28"))
olAtt.Add (Range("D29"))
olAtt.Add (Range("D30"))
olAtt.Add (Range("D31"))
olAtt.Add (Range("D32"))
olAtt.Add (Range("D33"))
olAtt.Add (Range("D34"))
olAtt.Add (Range("D35"))
olAtt.Add (Range("D36"))
olAtt.Add (Range("D37"))
olAtt.Add (Range("D38"))
olAtt.Add (Range("D39"))
olAtt.Add (Range("D40"))
olAtt.Add (Range("D41"))
olAtt.Add (Range("D42"))
olAtt.Add (Range("D43"))
olAtt.Add (Range("D44"))
olAtt.Add (Range("D45"))
olAtt.Add (Range("D46"))
olAtt.Add (Range("D47"))
olAtt.Add (Range("D48"))
ElseIf (Range("A3") = Range("A47")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
olAtt.Add (Range("D27"))
olAtt.Add (Range("D28"))
olAtt.Add (Range("D29"))
olAtt.Add (Range("D30"))
olAtt.Add (Range("D31"))
olAtt.Add (Range("D32"))
olAtt.Add (Range("D33"))
olAtt.Add (Range("D34"))
olAtt.Add (Range("D35"))
olAtt.Add (Range("D36"))
olAtt.Add (Range("D37"))
olAtt.Add (Range("D38"))
olAtt.Add (Range("D39"))
olAtt.Add (Range("D40"))
olAtt.Add (Range("D41"))
olAtt.Add (Range("D42"))
olAtt.Add (Range("D43"))
olAtt.Add (Range("D44"))
olAtt.Add (Range("D45"))
olAtt.Add (Range("D46"))
olAtt.Add (Range("D47"))
ElseIf (Range("A3") = Range("A46")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
olAtt.Add (Range("D27"))
olAtt.Add (Range("D28"))
olAtt.Add (Range("D29"))
olAtt.Add (Range("D30"))
olAtt.Add (Range("D31"))
olAtt.Add (Range("D32"))
olAtt.Add (Range("D33"))
olAtt.Add (Range("D34"))
olAtt.Add (Range("D35"))
olAtt.Add (Range("D36"))
olAtt.Add (Range("D37"))
olAtt.Add (Range("D38"))
olAtt.Add (Range("D39"))
olAtt.Add (Range("D40"))
olAtt.Add (Range("D41"))
olAtt.Add (Range("D42"))
olAtt.Add (Range("D43"))
olAtt.Add (Range("D44"))
olAtt.Add (Range("D45"))
olAtt.Add (Range("D46"))
ElseIf (Range("A3") = Range("A45")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
olAtt.Add (Range("D27"))
olAtt.Add (Range("D28"))
olAtt.Add (Range("D29"))
olAtt.Add (Range("D30"))
olAtt.Add (Range("D31"))
olAtt.Add (Range("D32"))
olAtt.Add (Range("D33"))
olAtt.Add (Range("D34"))
olAtt.Add (Range("D35"))
olAtt.Add (Range("D36"))
olAtt.Add (Range("D37"))
olAtt.Add (Range("D38"))
olAtt.Add (Range("D39"))
olAtt.Add (Range("D40"))
olAtt.Add (Range("D41"))
olAtt.Add (Range("D42"))
olAtt.Add (Range("D43"))
olAtt.Add (Range("D44"))
olAtt.Add (Range("D45"))
ElseIf (Range("A3") = Range("A44")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
olAtt.Add (Range("D27"))
olAtt.Add (Range("D28"))
olAtt.Add (Range("D29"))
olAtt.Add (Range("D30"))
olAtt.Add (Range("D31"))
olAtt.Add (Range("D32"))
olAtt.Add (Range("D33"))
olAtt.Add (Range("D34"))
olAtt.Add (Range("D35"))
olAtt.Add (Range("D36"))
olAtt.Add (Range("D37"))
olAtt.Add (Range("D38"))
olAtt.Add (Range("D39"))
olAtt.Add (Range("D40"))
olAtt.Add (Range("D41"))
olAtt.Add (Range("D42"))
olAtt.Add (Range("D43"))
olAtt.Add (Range("D44"))
ElseIf (Range("A3") = Range("A43")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
olAtt.Add (Range("D27"))
olAtt.Add (Range("D28"))
olAtt.Add (Range("D29"))
olAtt.Add (Range("D30"))
olAtt.Add (Range("D31"))
olAtt.Add (Range("D32"))
olAtt.Add (Range("D33"))
olAtt.Add (Range("D34"))
olAtt.Add (Range("D35"))
olAtt.Add (Range("D36"))
olAtt.Add (Range("D37"))
olAtt.Add (Range("D38"))
olAtt.Add (Range("D39"))
olAtt.Add (Range("D40"))
olAtt.Add (Range("D41"))
olAtt.Add (Range("D42"))
olAtt.Add (Range("D43"))
ElseIf (Range("A3") = Range("A42")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
olAtt.Add (Range("D27"))
olAtt.Add (Range("D28"))
olAtt.Add (Range("D29"))
olAtt.Add (Range("D30"))
olAtt.Add (Range("D31"))
olAtt.Add (Range("D32"))
olAtt.Add (Range("D33"))
olAtt.Add (Range("D34"))
olAtt.Add (Range("D35"))
olAtt.Add (Range("D36"))
olAtt.Add (Range("D37"))
olAtt.Add (Range("D38"))
olAtt.Add (Range("D39"))
olAtt.Add (Range("D40"))
olAtt.Add (Range("D41"))
olAtt.Add (Range("D42"))
ElseIf (Range("A3") = Range("A41")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
olAtt.Add (Range("D27"))
olAtt.Add (Range("D28"))
olAtt.Add (Range("D29"))
olAtt.Add (Range("D30"))
olAtt.Add (Range("D31"))
olAtt.Add (Range("D32"))
olAtt.Add (Range("D33"))
olAtt.Add (Range("D34"))
olAtt.Add (Range("D35"))
olAtt.Add (Range("D36"))
olAtt.Add (Range("D37"))
olAtt.Add (Range("D38"))
olAtt.Add (Range("D39"))
olAtt.Add (Range("D40"))
olAtt.Add (Range("D41"))
ElseIf (Range("A3") = Range("A40")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
olAtt.Add (Range("D27"))
olAtt.Add (Range("D28"))
olAtt.Add (Range("D29"))
olAtt.Add (Range("D30"))
olAtt.Add (Range("D31"))
olAtt.Add (Range("D32"))
olAtt.Add (Range("D33"))
olAtt.Add (Range("D34"))
olAtt.Add (Range("D35"))
olAtt.Add (Range("D36"))
olAtt.Add (Range("D37"))
olAtt.Add (Range("D38"))
olAtt.Add (Range("D39"))
olAtt.Add (Range("D40"))
ElseIf (Range("A3") = Range("A39")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
olAtt.Add (Range("D27"))
olAtt.Add (Range("D28"))
olAtt.Add (Range("D29"))
olAtt.Add (Range("D30"))
olAtt.Add (Range("D31"))
olAtt.Add (Range("D32"))
olAtt.Add (Range("D33"))
olAtt.Add (Range("D34"))
olAtt.Add (Range("D35"))
olAtt.Add (Range("D36"))
olAtt.Add (Range("D37"))
olAtt.Add (Range("D38"))
olAtt.Add (Range("D39"))
ElseIf (Range("A3") = Range("A38")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
olAtt.Add (Range("D27"))
olAtt.Add (Range("D28"))
olAtt.Add (Range("D29"))
olAtt.Add (Range("D30"))
olAtt.Add (Range("D31"))
olAtt.Add (Range("D32"))
olAtt.Add (Range("D33"))
olAtt.Add (Range("D34"))
olAtt.Add (Range("D35"))
olAtt.Add (Range("D36"))
olAtt.Add (Range("D37"))
olAtt.Add (Range("D38"))
ElseIf (Range("A3") = Range("A37")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
olAtt.Add (Range("D27"))
olAtt.Add (Range("D28"))
olAtt.Add (Range("D29"))
olAtt.Add (Range("D30"))
olAtt.Add (Range("D31"))
olAtt.Add (Range("D32"))
olAtt.Add (Range("D33"))
olAtt.Add (Range("D34"))
olAtt.Add (Range("D35"))
olAtt.Add (Range("D36"))
olAtt.Add (Range("D37"))
ElseIf (Range("A3") = Range("A36")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
olAtt.Add (Range("D27"))
olAtt.Add (Range("D28"))
olAtt.Add (Range("D29"))
olAtt.Add (Range("D30"))
olAtt.Add (Range("D31"))
olAtt.Add (Range("D32"))
olAtt.Add (Range("D33"))
olAtt.Add (Range("D34"))
olAtt.Add (Range("D35"))
olAtt.Add (Range("D36"))
ElseIf (Range("A3") = Range("A35")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
olAtt.Add (Range("D27"))
olAtt.Add (Range("D28"))
olAtt.Add (Range("D29"))
olAtt.Add (Range("D30"))
olAtt.Add (Range("D31"))
olAtt.Add (Range("D32"))
olAtt.Add (Range("D33"))
olAtt.Add (Range("D34"))
olAtt.Add (Range("D35"))
ElseIf (Range("A3") = Range("A34")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
olAtt.Add (Range("D27"))
olAtt.Add (Range("D28"))
olAtt.Add (Range("D29"))
olAtt.Add (Range("D30"))
olAtt.Add (Range("D31"))
olAtt.Add (Range("D32"))
olAtt.Add (Range("D33"))
olAtt.Add (Range("D34"))
ElseIf (Range("A3") = Range("A33")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
olAtt.Add (Range("D27"))
olAtt.Add (Range("D28"))
olAtt.Add (Range("D29"))
olAtt.Add (Range("D30"))
olAtt.Add (Range("D31"))
olAtt.Add (Range("D32"))
olAtt.Add (Range("D33"))
ElseIf (Range("A3") = Range("A32")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
olAtt.Add (Range("D27"))
olAtt.Add (Range("D28"))
olAtt.Add (Range("D29"))
olAtt.Add (Range("D30"))
olAtt.Add (Range("D31"))
olAtt.Add (Range("D32"))
ElseIf (Range("A3") = Range("A31")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
olAtt.Add (Range("D27"))
olAtt.Add (Range("D28"))
olAtt.Add (Range("D29"))
olAtt.Add (Range("D30"))
olAtt.Add (Range("D31"))
ElseIf (Range("A3") = Range("A30")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
olAtt.Add (Range("D27"))
olAtt.Add (Range("D28"))
olAtt.Add (Range("D29"))
olAtt.Add (Range("D30"))
ElseIf (Range("A3") = Range("A29")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
olAtt.Add (Range("D27"))
olAtt.Add (Range("D28"))
olAtt.Add (Range("D29"))
ElseIf (Range("A3") = Range("A28")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
olAtt.Add (Range("D27"))
olAtt.Add (Range("D28"))
ElseIf (Range("A3") = Range("A27")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
olAtt.Add (Range("D27"))
ElseIf (Range("A3") = Range("A26")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
olAtt.Add (Range("D26"))
ElseIf (Range("A3") = Range("A25")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
olAtt.Add (Range("D25"))
ElseIf (Range("A3") = Range("A24")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
olAtt.Add (Range("D24"))
ElseIf (Range("A3") = Range("A23")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
olAtt.Add (Range("D23"))
ElseIf (Range("A3") = Range("A22")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
olAtt.Add (Range("D22"))
ElseIf (Range("A3") = Range("A21")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
olAtt.Add (Range("D21"))
ElseIf (Range("A3") = Range("A20")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
olAtt.Add (Range("D20"))
ElseIf (Range("A3") = Range("A19")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
olAtt.Add (Range("D19"))
ElseIf (Range("A3") = Range("A18")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
olAtt.Add (Range("D18"))
ElseIf (Range("A3") = Range("A17")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
olAtt.Add (Range("D17"))
ElseIf (Range("A3") = Range("A16")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
olAtt.Add (Range("D16"))
ElseIf (Range("A3") = Range("A15")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
olAtt.Add (Range("D15"))
ElseIf (Range("A3") = Range("A14")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
olAtt.Add (Range("D14"))
ElseIf (Range("A3") = Range("A13")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
olAtt.Add (Range("D13"))
ElseIf (Range("A3") = Range("A12")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
olAtt.Add (Range("D12"))
ElseIf (Range("A3") = Range("A11")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
olAtt.Add (Range("D11"))
ElseIf (Range("A3") = Range("A10")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
olAtt.Add (Range("D10"))
ElseIf (Range("A3") = Range("A9")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
olAtt.Add (Range("D9"))
ElseIf (Range("A3") = Range("A8")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
olAtt.Add (Range("D8"))
ElseIf (Range("A3") = Range("A7")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
olAtt.Add (Range("D7"))
ElseIf (Range("A3") = Range("A6")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
olAtt.Add (Range("D6"))
ElseIf (Range("A3") = Range("A5")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
olAtt.Add (Range("D5"))
ElseIf (Range("A3") = Range("A4")) Then
olAtt.Add (Range("D3"))
olAtt.Add (Range("D4"))
Else
olAtt.Add (Range("D3"))
End If

olMsg.To = Range("A3")
olMsg.CC = ""
olMsg.BCC = "chris.mcafee@ge.com"
olMsg.Subject = Range("B3")
olMsg.Body = Range("C3")
olMsg.Send

Call MoveAndHighlightEmailedCells
Do Until Range("A3") = "Email Address/es Here"
Call SendEmail
Loop
End Sub


I desperately need to shorten this code because I need to build it so that I can attach up to 100 attachments.

PLEASE HELP:-)
Thanks,
Chris McAfee
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
This may be different than the way you have it set up, but it would seem that you could set up a loop to get all of the attachments.

If I was doing something similar, I would have it set up something like you do, but I would have the attachments in columns D to CY (if you had 100 attachments).

Then, this is what my code would look like...

Code: 
Sub SendEmail()
Dim olApp As Outlook.Application
Dim olAtt As Outlook.Attachments
Dim olMsg As Outlook.MailItem


Set olApp = CreateObject("Outlook.Application")
Set olMsg = olApp.CreateItem(olMailItem)
Set olAtt = olMsg.Attachments

Dim r,c as long 'r for row variable and c for column variable
r=2
c=4
Dim strTo, strSub, strBody as string

Do Until r>ActiveSheet.UsedRange.Rows.Count
strTo = ActiveSheet.Cells(r, 1)
strSub= ActiveSheet.Cells(r, 2)
strBody= ActiveSheet.Cells(r, 3)

olMsg.To = strTo
olMsg.Subject = strSub
olMsg.Body = strBody

Do Until ActiveSheet.Cells(x,c)=""
olAtt.Add ActiveSheet.Cells(x,c)
c=c+1
Loop

olMsg.Send

Call MoveAndHighlightEmailedCells
    Do Until Range("A3") = "Email Address/es Here"
    Call SendEmail
    Loop
End Sub

Please know that I haven't tested this, but this is the way I would do the code if I was doing the same thing.

Hopefully it will get you going in the right direction.
 
Upvote 0
Sorry... Forgot to add a Loop for the outer loop. Here it is again.

Code: 
Sub SendEmail()
Dim olApp As Outlook.Application
Dim olAtt As Outlook.Attachments
Dim olMsg As Outlook.MailItem


Set olApp = CreateObject("Outlook.Application")
Set olMsg = olApp.CreateItem(olMailItem)
Set olAtt = olMsg.Attachments

Dim r,c as long 'r for row variable and c for column variable
r=2
c=4
Dim strTo, strSub, strBody as string

Do Until r>ActiveSheet.UsedRange.Rows.Count
strTo = ActiveSheet.Cells(r, 1)
strSub= ActiveSheet.Cells(r, 2)
strBody= ActiveSheet.Cells(r, 3)

olMsg.To = strTo
olMsg.Subject = strSub
olMsg.Body = strBody

Do Until ActiveSheet.Cells(x,c)=""
olAtt.Add ActiveSheet.Cells(x,c)
c=c+1
Loop

olMsg.Send

r = r + 1
Loop

Call MoveAndHighlightEmailedCells
    Do Until Range("A3") = "Email Address/es Here"
    Call SendEmail
    Loop
End Sub
 
Upvote 0
Dad_x6 said:
Sorry... Forgot to add a Loop for the outer loop. Here it is again.

Code: 
Sub SendEmail()
Dim olApp As Outlook.Application
Dim olAtt As Outlook.Attachments
Dim olMsg As Outlook.MailItem


Set olApp = CreateObject("Outlook.Application")
Set olMsg = olApp.CreateItem(olMailItem)
Set olAtt = olMsg.Attachments

Dim r,c as long 'r for row variable and c for column variable
r=2
c=4
Dim strTo, strSub, strBody as string

Do Until r>ActiveSheet.UsedRange.Rows.Count
strTo = ActiveSheet.Cells(r, 1)
strSub= ActiveSheet.Cells(r, 2)
strBody= ActiveSheet.Cells(r, 3)

olMsg.To = strTo
olMsg.Subject = strSub
olMsg.Body = strBody

Do Until ActiveSheet.Cells(x,c)=""
olAtt.Add ActiveSheet.Cells(x,c)
c=c+1
Loop

olMsg.Send

r = r + 1
Loop

Call MoveAndHighlightEmailedCells
    Do Until Range("A3") = "Email Address/es Here"
    Call SendEmail
    Loop
End Sub
Click to expand...

Thanks for the response. How would you easily copy the path of the pdfs (invoices) into column D all the way to column CY?
 
Upvote 0
You must log in or register to reply here.

Similar threads

gtd526
  • Question Question
vba Sorting Columns w/ 2 Criteria's
Replies
1
Views
320
gtd526
gtd526
G
  • Question Question
Creating a Word Doc using Excel VBA
Replies
2
Views
264
rikvny02
R
D
  • Solved
Quicker VBA
Replies
1
Views
217
Georgiboy
Georgiboy
gtd526
  • Question Question
vba selecting a specific range if ISNUMBER
Replies
1
Views
351
gtd526
gtd526
K
  • Question Question
Populate With Criteria
Replies
3
Views
60
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top