Can you have a VBA send an email with cell ref CC too?

[bemcbride]

Hello so I have the below right now and it's working great but now I want to add in that it also CC's another email when sending.

CC email address will be entered in cell L4. If it's not possible to do a CC like this is there a way that I can just have it sent to the company@company.com and to cell email L4?

(I tried this but it didn't work - If ans = vbYes Then ActiveWorkbook.SendMail "Company@Company.com, Range("L4").Value", "AR Refund request for " .....)


Sub ARSubmit()
'
' ARSubmit Macro
Dim ans As VbMsgBoxResult
If Range("E5").Value Like "*OK to submit*" Then
ans = MsgBox("Are you ready to submit Check/Wire refund request?" & vbLf & vbLf & "Clicking Yes will send an email to Shared Services for processing of your refund.", vbYesNo, "Company Refund Department")
If ans = vbYes Then ActiveWorkbook.SendMail "Company@Company.com", "AR Refund request for " & Range("E21").Value
Else
MsgBox "You have not completed all the minimum required fields."
End If


'
End Sub

 

[James Snyder]

You can add a cc to also send things to:

'##########################################################################
'##  SendEmail
'##########################################################################
'
' Purpose:  Send reports with company signature by email
'
' Created:  06/17/2013
' Author:   Jim Snyder
'
' Inputs:   References to data from sheet1
'
' Outputs:  Emailed report
'
'
Private Function SendEmail(toSrc As Range, _
    ccSrc As Range, _
    fromList As String, _
    subjText As String, _
    bodyText As String, _
    attachment As String, _
    userName As String, _
    passWord As String) As Boolean


    Dim toList As String
    Dim ccList As String
    Dim config As Object
    Dim msgStruct As Object
    Dim addressee As Variant
    Dim cellRef As Range
    Const cdoBasic = 1

    '  Build recipient list
    For Each cellRef In toSrc
        If Len(toList) > 1 Then
            toList = toList & ";" & cellRef.Value
        Else
            toList = toList & cellRef.Value
        End If
    Next cellRef

    '  Build recipient list
    For Each cellRef In ccSrc
        If Len(cellRef) > 1 Then
            ccList = ccList & ";" & cellRef.Value
        Else
            ccList = ccList & cellRef.Value
        End If
    Next cellRef

    Set msgStruct = CreateObject("CDO.Message")
    Set config = CreateObject("CDO.Configuration")

    With config
        .Fields(cdoSendUsingMethod) = cdoSendUsingPort
        .Fields(cdoSMTPServer) = "smtp.mybullseyeonline.com"
        .Fields(cdoSMTPServerPort) = 5125
        .Fields(cdoSMTPAuthenticate) = cdoBasic
        .Fields(cdoSendUserName) = userName
        .Fields(CdoSendPassword) = passWord
        .Fields.Update
    End With

    With msgStruct
        Set .Configuration = config
        .From = fromList
        .To = toList
        If Len(ccList) > 0 Then
            .Cc = ccList
        End If
        .Subject = subjText
        .HTMLBody = bodyText
        .AddAttachment attachment
        .Send
    End With
    
    Set config = Nothing
    Set msgStruct = Nothing
    SendEmail = True
End Function    ' SendMail

 

[bemcbride]

That looks really complicated. Will that still do what my previous one did because it looks like a lot.

I just thought that adding in a CC or an extra To would be something less complex and long.

 

[James Snyder]

It doesn't use Outlook, so it will be a bit more complex than what you have. The .SendMail function would need to be researched to see if it has any other parameters you could use.

 

[James Snyder]

The answer is "no, you can't". Try this topic:
http://www.mrexcel.com/forum/excel-questions/533269-can-you-cc-using-sendmail.html

 

[bemcbride]

But you can send to multiple To's. How do i do that?

I tried doing Array () but it said I had an invalid email listed...I'm thinking it's because I just had L4 instead of an email.

 

[James Snyder]

Look at my code supplied above. It handles multiple "To:" and multiple "Cc:"