Macro to pick up email address within SS

[Dan190984]

Hi all

I am currently running the below script to send a spreadsheet to a designated email address within a ss, only issue is I have no idea what I am doing!

How do I get the script to pick up the changing email address which will always be in G45 on the active worksheet.

Sub Understood()
'Working in 97-2010
Dim wb As Workbook
Dim I As Long
Set wb = ActiveWorkbook
If Val(Application.Version) >= 12 Then
If wb.FileFormat = 51 And wb.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will" & vbNewLine & _
"be no VBA code in the file you send. Save the" & vbNewLine & _
"file first as xlsm and then try the macro again.", vbInformation
Exit Sub
End If
End If
On Error Resume Next
For I = 1 To 3
wb.SendMail ActiveWorkbook.Sheets("Sheet1").Range("G45").Value, _
"Subject"
If Err.Number = 0 Then Exit For
Next I
On Error GoTo 0
End Sub

 

[AlphaFrog]

Try something like this...

wb.SendMail ActiveWorkbook.[COLOR="Red"]ActiveSheet[/COLOR].Range("G45").Value, _
            "Subject"

 

[Dan190984]

Thanks thats excellent. Also - how would I include a CC in the email?

 

[AlphaFrog]

I don't think you can CC someone using the SendMail method. You can have more than one "TO:" recipients though.

wb.SendMail ActiveWorkbook.ActiveSheet.Range("[COLOR="Red"]G45:G46[/COLOR]").Value, _
            "Subject"

Otherwise if you must use CC, then you would have to send the email using another method.
Ron de Bruin's Example Code for sending mail from Excel