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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
vba Sorting Columns w/ 2 Criteria's
Replies
1
Views
197
gtd526
gtd526
D
  • Solved
Quicker VBA
Replies
1
Views
142
Georgiboy
Georgiboy
gtd526
  • Question
vba selecting a specific range if ISNUMBER
Replies
1
Views
220
gtd526
gtd526
gtd526
  • Solved
Move selected row to top of range
Replies
6
Views
215
gtd526
gtd526
P
  • Solved
Counter in For Each loop
Replies
2
Views
273
Possum17
P

Forum statistics

Threads
1,216,746
Messages
6,132,477
Members
449,729
Latest member
davelevnt

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