VBA Excel

[yaljubaili]

I send every month new updated information about the market to the clients by email, how can I put the clients list in BCC, put my manager CC, and attach PDFs about the market updated information, by using VBA Excel.

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:
VBA Excel
VBA Excel - OzGrid Free Excel/VBA Help Forum
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[chris smith5]

Hi,
To send emails with specific recipients, CC, BCC, and attachments using VBA in Excel, you can use Microsoft Outlook. Here's an example of how you can do this:

Sub SendEmail()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim RecipientList As Range
    Dim AttachmentPath As String
    Dim ManagerEmail As String
    
    ' Set the recipient list range containing client email addresses
    Set RecipientList = ThisWorkbook.Sheets("Sheet1").Range("A2:A10")
    
    ' Set the path of the PDF file(s) to be attached
    AttachmentPath = "C:\Path\to\PDF\File.pdf"
    
    ' Set the manager's email address for CC
    ManagerEmail = "manager@example.com"
    
    ' Create Outlook instance
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    ' Loop through each recipient and send email
    For Each Recipient In RecipientList
        With OutMail
            ' Set the email properties
            .To = Recipient.Value
            .CC = ManagerEmail
            .BCC = ""
            .Subject = "Monthly Market Update"
            .Body = "Dear Client, please find attached the latest market update."
            
            ' Attach the PDF file(s)
            .Attachments.Add AttachmentPath
            
            ' Send the email
            .Send
        End With
    Next Recipient
    
    ' Release the Outlook objects
    Set OutMail = Nothing
    Set OutApp = Nothing
    
    MsgBox "Emails have been sent successfully."
End Sub

Make sure to adjust the following parts according to your requirements.

Best Regards.