VBA - Mail whole workbook to multiple people seperately

[AKAvenger]

Hello Hello Excel Geniuses

I have found MANY answers to the first part of my problem.....
I would like to email my full workbook to multiple users [solved] BUT...I would like to e-mail it to each individual separately.....

the below is working for multiple and 1 e-mail recipient if you are sending 1 e-mail. this is from Ron de Bruins excel tips....

Sub Mail_workbook_Outlook_1()
'Working in Excel 2000-2016
'This example send the last saved version of the Activeworkbook
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
    Dim OutApp As Object
    Dim OutMail As Object


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)


    On Error Resume Next
    With OutMail
        .to = “e-mail address is placed here”
        .CC = ""
        .BCC = ""
        .Subject = "MEA | Product-Sales Opportunity Joint Pipeline"
        .Body = "Dear Colleagues, find attached, updated pipeline."
        .Attachments.Add ActiveWorkbook.FullName
        'You can add other files also like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
        End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[Macropod]

Is there a reason for not just sending it once to all recipients as BCC?

 

[Comfy]

Either way Ron De Bruin has a very extensive library of solutions for Excel/Outlook/VBA

https://www.rondebruin.nl/win/s1/outlook/mail.htm

In particular his Add-in: https://www.rondebruin.nl/win/addins/rdbmail.htm

But I would suggest you start with his RDBMail excel worksheet template: https://www.rondebruin.nl/win/winfiles/Install Mail Template Sheet for Excel 2007-2016.zip

 

[AKAvenger]

Hi Macropod
it is a quality instruction...I tried to argue, but no go.

 

[AKAvenger]

Hello Comfy
If I could get the RDBMail worksheet template to stop sending the actual template in the mail as an attachment then this would work like magic....but everytime I load the information and click on send. The mail includes the actual RDBMail file...which is not the point....or am I doing something wrong?

 

[Stiuart_W]

I think if you post YOUR actual code peeps could help.

I have a workbook that does exactly what you want and I just use a Do Loop to keep sending mails until the list of recipients is exhausted

 

[AKAvenger]

Hi Stiuart_W

I am using this code:
it works if you are sending to 1 person. but I need to send to many persons individually....
Can you help?

 

[Stiuart_W]

Put your code inside some code like below

for i = starting_row to ending_row

Your code

Next i

Change “e-mail address is placed here” to cells(i,column_where_mails_are_stored).

But as was mentioned in the first post the BEST way to achieve what you want is to send the e-mail To: yourself AND BCC to the rest of the group. That way you only send 1 e-mail rather than many

 

[Logit]

.
This project will email to as many folks as you need - allow you to choose which attachment each person will receive - give you some option
for modifying the body.

Option Explicit


Sub PC_Email()
    
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim MailAttachments As String
    Dim cell As Variant '                             Not previously DIM'd
    
    Sheets("Sheet1").Select '                         Edit as required
    Range("A1").Select
    
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    
  
   On Error GoTo cleanup
    For Each cell In Columns("C").Cells
        If cell.Value Like "?*@?*.?*" And _
        LCase(Cells(cell.Row, "H").Value) = "yes" Then
        
        
    With Application.ActiveSheet
        MailAttachments = Cells(cell.Row, "G").Value
    End With
        
    
    Set OutMail = OutApp.CreateItem(0)
        
            On Error Resume Next
                              
            With OutMail
            
            strbody = "Hi " & Cells(cell.Row, "B") & "," & vbNewLine & vbNewLine & _
              "The " & Cells(cell.Row, "A") & " ACH Remittance for " & Cells(cell.Row, "D") & " is attached." & vbNewLine & _
              "Please let me know if you have any questions." & vbNewLine & vbNewLine & _
              "Thanks," & vbNewLine & vbNewLine & _
              "Accounts Payable" & vbNewLine & "Reily Foods"
              
                .To = cell.Value
                .Subject = Cells(cell.Row, "A") & " ACH Remittance"
                .Body = strbody
               
                .Attachments.Add MailAttachments
                
                .Display  'Or use .Send
                  
                
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
    Next cell




cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
    
End Sub

Excel 2007 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]

A​

[/TH]
[TH]

B​

[/TH]
[TH]

C​

[/TH]
[TH]

D​

[/TH]
[TH]

E​

[/TH]
[TH]

F​

[/TH]
[TH]

G​

[/TH]
[TH]

H​

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

1​

[/TD]
[TD]Laptop[/TD]
[TD]Mr. Me[/TD]
[TD]me@yahoo.com[/TD]
[TD]Asus 101[/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\My\Desktop\Laptops.txt[/TD]
[TD]no[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

2​

[/TD]
[TD]Books[/TD]
[TD]Mr. You[/TD]
[TD]you@yahoo.com[/TD]
[TD]Dell BDA[/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\My\Desktop\Excel.txt[/TD]
[TD]yes[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

 

[AKAvenger]

Hey Stiuart_W
I must be going mad my friend because I cannot make sense from your reply...so sorry, would you be able to add what you mean to my existing code?
I apologise but perhaps I am not as good with improvising....