Creating Email Message Macro Help

[cloyd1980]

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

 

[Norie]

Try something like this.

' other declarations
Dim arrAdds()
Dim cnt As Long

    ReDim arrAdds(1 To LastRow

    For i = 2 To LastRow
        Set rngAdd = wsEMAdd.Range("d" & I)

        If rngAdd.Value <> "" Then
            cnt = cnt+1
            arrAdds(cnt) = rngAdd.Value
        End If
    Next I

    If cnt>0 Then
        ReDim Preserve arrAdds(1 To cnt)
        MailAdd = Join(arrAdds, ";")
    Else
        MsgBox "No email addresses found!"
        Exit Sub
    End If

    olMail.BCC = MailAdd

 

[Michael M]

maybe like this

Sub SendEmail()
Dim olApp As Object, olMail As Object, LastRow As Long, i As Long
Dim wb As Workbook, wsEMAdd As Worksheet
Dim MailAdd As String, rngAdd As String
Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(0)
Set wb = ActiveWorkbook
Set wsEMAdd = wb.Worksheets("Email Addresses")
LastRow = wsEMAdd.Cells(Rows.Count, "D").End(xlUp).Row
    For i = 2 To LastRow
        rngAdd = Range("d" & i).Value
        MailAdd = MailAdd & ";" & rngAdd
    Next
olMail.BCC = MailAdd
olMail.Display
End Sub

 

[cloyd1980]

maybe like this

Sub SendEmail()
Dim olApp As Object, olMail As Object, LastRow As Long, i As Long
Dim wb As Workbook, wsEMAdd As Worksheet
Dim MailAdd As String, rngAdd As String
Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(0)
Set wb = ActiveWorkbook
Set wsEMAdd = wb.Worksheets("Email Addresses")
LastRow = wsEMAdd.Cells(Rows.Count, "D").End(xlUp).Row
    For i = 2 To LastRow
        rngAdd = Range("d" & i).Value
        MailAdd = MailAdd & ";" & rngAdd
    Next
olMail.BCC = MailAdd
olMail.Display
End Sub

Michael,

Thank you very much, that works perfectly! The only thing I don't understand, and was hoping you could explain, is the line:
MailAdd = MailAdd & ";" & rngAdd

What does MailAdd = MailAdd do when it is not "set" to anything and why put the semicolon in front of the email address?

Thanks for the help!
Corey

 

[Michael M]

It just means ....use the previous value for Mailadd and then add the rngadd value.
So each time it goes round the for loop, it increases the value of Mailadd.
If you step through the code manually using F8, and hover your mouse over Mailadd you will see it keeps adding on a new value.
You don't need to set Mailadd as it changes all the time !