Hi all,
I'm sure I'm missing something super easy and obvious, but I'm new to VBA and can't figure out what it is, so any help would be much appreciated!
I am trying to write a simple macro in excel that loops through all the cells with data in column D and puts the email address contained in each cell into the BCC of an outlook message. The code puts the first address in there, but does not put the semi-colon and does not put any other addresses. I also don't get any errors. What am I doing wrong?
Sub SendEmail()
Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Dim LastRow As Integer
Dim wb As Excel.Workbook
Dim wsEMAdd As Excel.Worksheet
Dim MailAdd As String
Dim rngAdd As Excel.Range
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
Set wb = ActiveWorkbook
Set wsEMAdd = wb.Worksheets("Email Addresses")
LastRow = wsEMAdd.Range("d10000").End(xlUp).Row
Set rngAdd = wsEMAdd.Range("d2")
MailAdd = rngAdd.Value
For i = 2 To LastRow
Do Until rngAdd = ""
olMail.BCC = MailAdd & ";"
Set rngAdd = rngAdd.Offset(0, 1)
Loop
Next
olMail.Display
End Sub
I'm sure I'm missing something super easy and obvious, but I'm new to VBA and can't figure out what it is, so any help would be much appreciated!
I am trying to write a simple macro in excel that loops through all the cells with data in column D and puts the email address contained in each cell into the BCC of an outlook message. The code puts the first address in there, but does not put the semi-colon and does not put any other addresses. I also don't get any errors. What am I doing wrong?
Sub SendEmail()
Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Dim LastRow As Integer
Dim wb As Excel.Workbook
Dim wsEMAdd As Excel.Worksheet
Dim MailAdd As String
Dim rngAdd As Excel.Range
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
Set wb = ActiveWorkbook
Set wsEMAdd = wb.Worksheets("Email Addresses")
LastRow = wsEMAdd.Range("d10000").End(xlUp).Row
Set rngAdd = wsEMAdd.Range("d2")
MailAdd = rngAdd.Value
For i = 2 To LastRow
Do Until rngAdd = ""
olMail.BCC = MailAdd & ";"
Set rngAdd = rngAdd.Offset(0, 1)
Loop
Next
olMail.Display
End Sub