Create outlook email from excel email list with one criteria with subject matter and body of email

[davodka]

Hi Everyone,

I have emails in column C and the criteria in column D would be a "Yes". So I want to email everyone in column C when there's a Yes in column D in one big email. Also I'd like the subject to be in cell D1 and the body of the email in D2.

Doable?

Thanks,

Michael

 

[davodka]

Michael,

At any one time, a list of 30-40 email addresses may receive any of the reports. How the setup is done only looks at one person per company when the criteria needs to be inclusive of anyone with a check next to it. Do you think that's doable?

Thanks,

Michael Nien

 

[Michael M]

Your original data setout makes it difficult to run it that way.
With the recipient list constantly up for change, it's going to get hard to select the recipients.
It means every time you want to send a series of E-Mails you'll have to keep changing the checklist.....it kind of defeats the benefit of the macro
I'd seriously consider changing to something closer to my suggestion.

Mayber even consider a recipient list on Sheet 2, with the checks next to it and the company name that the list refers to ....then it can access the data on Sheet one in the code.

 

[davodka]

My objective is to run the macros based on individual company. So when I'm done with the Toys r us reports, I can just send the email to that list of people. And I would set up like 20 buttons for all the different companies. Do you think then that would be doable? In rondebruin's website, he was able to achieve this, however the problem was that it was sending out multiple emails and since I do not know how to write my own codes, I thought there was a way to tweek it to make it work.

Thanks.

 

[Michael M]

Yes, I believe it's doable, but from memory, his multiple E-mails are a consistent list, not a dynamic one.
Why is there a problem with sending multiple E-Mails ?
Once the .Display line in the code is changed to .Send you won't see it happen ?

Having a button for each company is also doable, but you will have to create a list of recipients for each company, and it appears you want that list to change regularly.

 

[davodka]

made it work....muahahahaaaa!!! so the check marks in column D with the check marks and the emails in column C, I copy the formula in column AJ to pull the emails, the formulas and code is below

Formula in column AJ

+IFERROR(IF(MATCH("x",D3,0)>0,$C3,""),"")

The code is:

Sub Mail_workbook_Outlook_1()
'Working in 2000-2010
'This example send the last saved version of the Activeworkbook
Dim OutApp As Object
Dim OutMail As Object
Dim emailRng As Range, cl As Range
Dim sTo As String
Set emailRng = Worksheets("DistributionMatrix").Range("AK3:AK100")
For Each cl In emailRng
sTo = sTo & ";" & cl.Value
Next
sTo = Mid(sTo, 2)
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = sTo
.CC = ""
.BCC = ""
.Subject = "RMA #" & Worksheets("RMA").Range("E1")
.Body = "Attached to this email is RMA #" & _
Worksheets("RMA").Range("E1") & _
". Please follow the instructions for your department included in this form."
.Attachments = ""
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[Michael M]

I think you'll find this a little quicker as it picks up the entire range of addresses in one go.
It also sets the Outmail once only
However, that seems to be a different result to the question you first posed...

But, if it works....

Sub Mail_workbook_Outlook_1()
'Working in 2000-2010
'This example send the last saved version of the Activeworkbook
Dim OutApp As Object, OutMail As Object, cl As Range, sTo As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
sTo = Join(Application.WorksheetFunction.Transpose(Worksheets("DistributionMatrix").Range("AK3:AK100")), ";")
On Error Resume Next
With OutMail
.To = sTo
.CC = ""
.BCC = ""
.Subject = "RMA #" & Worksheets("RMA").Range("E1")
.Body = "Attached to this email is RMA #" & _
Worksheets("RMA").Range("E1") & _
". Please follow the instructions for your department included in this form."
.Attachments = ""
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'CHANGE THIS LINE TO .SEND TO SEND AUTOMATICALLY
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub