Help with VBA Code (sending automated email with outlook)

[Rodney Coello]

Hello,

I want to set up a VBA Code that will send a mass email to several email recipients. can someone please help. I have attached an excel file to use as example. thank you.

this is the code I have so far. what am I doing wrong? thanks also I have attached excel file for example.

Sub GL_Central_Texas(what_address As String, subject_line As String, mail_body As String)

Dim OlApp As Outlook.Application
Set OlMail = CreateObject(Outlook.Application)

Dim OlMail As Outlook.MailItem
Set OlMail = OlApp.CreateItem(olMailItem)

  .Subject = "October Home Health & Hospice GL Support"
  .Body = "Attached are the HCHB reports and the Horizon GL Excel file to support your uploads to your GL."
  .Display
row_number = 1
  Do
  DoEvents
  Loop Until row_number = 4


End Sub

 

[Rodney Coello]

I also tried setting up a code to send an email to a distribution list on my outlook contacts. my contact group name is "CBO Finance Team". I set this code up but again it came up with errors. i don't want the code to necessarily send email but to say the same thing each time, so i wanted to set up a macro that I can send to contact group and say the same thing. what am I doing wrong? thank you!

Sub GL_Central_Texas()
Dim OlApp As Outlook.Application
Dim OlMail As Outlook.AddressList

Set OlApp = New Outlook.Application
Set OlMail = OlApp.CreateItem(olMailItem)

With OlMail
.To = "CBO Finance team"


.Subject = "Home Health & Hospice GL Support"
.Body = "Attached are the HCHB reports and the Horizon GL Excel file to support your uploads to your GL."
.Display
End With



End Sub

 

[Rodney Coello]

I tried to reply to my other post in which I had a dublicate post however could not reply, so i'm replying here. sorry for posting duplicate. the error that I get when I run the code above is "Run-time error '13' type mismatch. when I click debug it has highlighted "Set OlMail = OlApp.CreateItem(olMailItem)" I can create a code for sending an email to one person, but I tried to create one for a contact group I have set up in outlook and i'm getting stuck. Sorry but i'm not an expert in VBA so I was hoping I could get a direction. I copied the code below again.

code (vb):


Sub GL_Central_Texas()

Dim OlApp As Outlook.Application
Dim OlMail As Outlook.AddressList

Set OlApp = New Outlook.Application
Set OlMail = OlApp.CreateItem(olMailItem)

With OlMail
.To = "CBO Finance team"


.Subject = "Home Health & Hospice GL Support"
.Body = "Attached are the HCHB reports and the Horizon GL Excel file to support your uploads to your GL."
.Display
End With



End Sub

 

[Logit]

Paste this into a ROUTINE MODULE :

Option Explicit

Sub Send_Email()

    Dim c As Range
    Dim OutLookApp As Object
    Dim OutLookMailItem As Object
    Dim i As Integer
   
    For Each c In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Cells
        Set OutLookApp = CreateObject("Outlook.application")
        Set OutLookMailItem = OutLookApp.CreateItem(0)
        With OutLookMailItem
                .To = c.Value
                .CC = "Your CC here"
                .BCC = "test"
                .Subject = "Your Subject here"
                .HTMLBody = "Your Body content here"
                '.Attachments.Add c.Offset(i, 1).Value
                .Display
                '.Send
        End With
    Next c

End Sub

If you want to add an attachment to email, uncomment this line in the macro :

'.Attachments.Add c.Offset(i, 1).Value

Email addresses go in Column A, beginning at A2. Full path to attachments go in Column B, beginning at B2.