Using VBA to send emails based on adjacent conditions

Question

I'm a new VBA user and am trying to accomplish what I've described in the title using the code below.

I think it has something to do with creating dims specifically for cc/bcc/and to, but I'm not quite sure. in one column is a list of emails that have been filtered for based on specific conditions and in the column right next to it is either "" "cc" or "bcc". If it's blank, then it goes into "to" if it's cc" it goes into the .CC field etc. etc.

Sub SendList()

'DIM

    Dim olApp As Outlook.Application
    Dim olMail As MailItem
    Dim CurrFile As String
    Dim emailRng As Range, cl As Range
    Dim sTo As String

'SET

    Set emailRng = ActiveSheet.Range("E3:E100").SpecialCells(xlCellTypeVisible)

    For Each cl In emailRng

    sTo = sTo & ";" & cl.Value

    Next

    sTo = Mid(sTo, 2)

    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)

'UPDATE WORKBOOK BEFORE SENDING

    ActiveWorkbook.Save
    CurrFile = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

'Need to find a way to automate to TO CC and BCC

    With olMail
        .To = sTo
        .CC = ""
        .BCC = ""
        .Subject = "Audit Report XYZ" & " " & "-" & " " & Date
        .Body = .Body & "Test" & vbCrLf & "Test2" & vbCrLf & "Test3"
        .Attachments.Add "C:\Users\uq050e\Downloads\anyfile.xlsx" 'An audit report
        .Display '.Send
    End With

    Set olMail = Nothing
    Set olApp = Nothing

End Sub

Answer 1

It looks like the problem is not with Outlook VBA, but with reading the Excel's content. I'd suggest learning VBA and Excel a bit first, see Getting Started with VBA in Excel 2010.

You can use the Text property of the Range class to get the text for the specified object/cell.