Macro to e-mail different sheets in a single mail to e-mail addresses present in excel sheet

[swetamies]

Existing Macro:- I have an existing macro with a send Email button on clicking it will send separate mails with single attachment to all the owner.present in the excel sheet.


Requirement:- while clicking on send mail button it should first pick an owner i.e. mail recipient from coloum D of "List of Data Providers" tab and then should look for all attachments associated with that so that instead of sending multiple mails to 1 person with a single attachment we can send a single mail with all the attachments associated with them.

I need this on urgent basis if anyone has any suggestions, I would love to hear them.

Columns from where I need to pick up the details are:-

[TABLE="width: 1198"]
<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]No[/TD]
[TD]RAS Metric[/TD]
[TD]Name[/TD]
[TD]Email[/TD]
[TD]CC[/TD]
[TD]Country[/TD]
[TD]Name2[/TD]
[/TR]
[TR]
[TD]6.2[/TD]
[TD]Regulatory Engagement[/TD]
[TD]Sweta[/TD]
[TD]Sweta.Gupta@gmail.com[/TD]
[TD] [/TD]
[TD]India[/TD]
[TD]Sweta Gupta[/TD]
[/TR]
[TR]
[TD]6.3[/TD]
[TD]Open Regulatory Findings[/TD]
[TD]Sweta[/TD]
[TD]Sweta.Gupta@gmail.com[/TD]
[TD] [/TD]
[TD]India[/TD]
[TD]Sweta Gupta[/TD]
[/TR]
[TR]
[TD]6.1[/TD]
[TD]Non-compliance with laws[/TD]
[TD]Sweta[/TD]
[TD]Sweta.Gupta@gmail.com[/TD]
[TD] [/TD]
[TD]India[/TD]
[TD]Sweta Gupta[/TD]
[/TR]
</tbody>[/TABLE]


Thanks,
Sweta

 

[Michael M]

It would help to see the existing macro....but something like this needs to be included BEFORE you loop through your recipient list.

ans = MsgBox("Will you need to add further attachments ??", vbYesNo)
        If ans = vbYes Then
            AttachFileName = Application.GetOpenFilename("Files (*.**)," & _
                                "*.**", 1, "Select File", "Open", True)

 

[swetamies]

It would help to see the existing macro....but something like this needs to be included BEFORE you loop through your recipient list.

ans = MsgBox("Will you need to add further attachments ??", vbYesNo)
        If ans = vbYes Then
            AttachFileName = Application.GetOpenFilename("Files (*.**)," & _
                                "*.**", 1, "Select File", "Open", True)

I don't want any pop up to ask for attachment, it just pick the attachments with a single mail id, attach all in one mail and send it automatically