Sending Email with Excel with To, CC & BCC

[mydragonstalents]

Hi Everyone,

I am just learning about VBA but I got the basics down.

I am trying to use our excel to make it faster to send emails to out clients. We have a To, CC and BCC emails.

Each line would be its own client with emails:

To: CC: BCC:
B3: Client 1 C3: Client 1 D3: Client 1
B4: Client 2 C4: Client 2 D4: Client 2
B5: Client 3 C5: Client 3 D5: Client 3

Would I need a button for each row/client?

I don't want to send it automatically because I have to attach a statement manually, but also the subject would change each month.

Subject: Fund (Month) (Year)

Hope you guys can help.

 

[igold]

Hi mydragonstalents,

With Outlook, here are the basics.

Sub eml()

    Dim objOutlook As Object
    Dim objMail As Object

    Set objOutlook = CreateObject("Outlook.Application")
    Set objMail = objOutlook.CreateItem(0)

    With objMail
        .To = Range("B3").Value
        .cc = Range("C3").Value
        .bcc = Range("D3").Value
        .Subject = "Fund " & Format(Date, "MMM") & " " & Format(Date, "YYYY")
        .Body = Range("YOUR Range").Value
        '.Attachments.Add rngAttach.Value
        .Display                   'Instead of .Display, you can use .Send to send the email _
                                         or .Save to save a copy in the drafts folder
    End With

    Set objOutlook = Nothing
    Set objMail = Nothing
    'Set rngTo = Nothing
    'Set rngSubject = Nothing
    'Set rngBody = Nothing
    'Set rngAttach = Nothing

End Sub

HTH

igold

 

[mydragonstalents]

I added a form control button and assigned it to the Macro but it's giving an error '400'.

Hi mydragonstalents,

With Outlook, here are the basics.

Sub eml()

    Dim objOutlook As Object
    Dim objMail As Object

    Set objOutlook = CreateObject("Outlook.Application")
    Set objMail = objOutlook.CreateItem(0)

    With objMail
        .To = Range("B3").Value
        .cc = Range("C3").Value
        .bcc = Range("D3").Value
        .Subject = "Fund " & Format(Date, "MMM") & " " & Format(Date, "YYYY")
        .Body = Range("YOUR Range").Value
        '.Attachments.Add rngAttach.Value
        .Display                   'Instead of .Display, you can use .Send to send the email _
                                         or .Save to save a copy in the drafts folder
    End With

    Set objOutlook = Nothing
    Set objMail = Nothing
    'Set rngTo = Nothing
    'Set rngSubject = Nothing
    'Set rngBody = Nothing
    'Set rngAttach = Nothing

End Sub

HTH

igold

 

[igold]

I am sorry, I don't know why it is throwing that error. I tested it with a basic worksheet and it went fine. Perhaps another user will chime in. Additionally, you may want to do some independent research. I do not believe it is code related but rather something with the environment.

igold

 

[mydragonstalents]

Actually after some tinkering it worked! I have everything set but how would I get the subject to say "Fund (Written Month) YYYY". I want the subject to have the previous month written out.

Private Sub CommandButton1_Click()
Dim objOutlook As Object
Dim objMail As Object

Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)

With objMail
.To = Range("B3").Value
.CC = Range("C3").Value
.BCC = Range("D3").Value
.Subject = "PMSBL Fund " & Format(Date, "MMM") & " " & Format(Date, "YYYY")
'.Attachments.Add rngAttach.Value
.Display

End With

Set objOutlook = Nothing
Set objMail = Nothing
'Set rngTo = Nothing
'Set rngSubject = Nothing
'Set rngBody = Nothing
'Set rngAttach = Nothing
End Sub

Thank you a tons BTW!

 

[igold]

Ah, before you thank me, let's see if this does it.

Please change this line:

.Subject = "PMSBL Fund " & Format(Date, "MMM") & " " & Format(Date, "YYYY")

To this:

 .Subject = "PMSBL Fund " & MonthName(Month(Date) - 1, False) & " " & Format(Date, "YYYY")

Hope this works for you!

igold