Email VBA pull email address from cell

[BadFish523]

Hello all,

I have this simple VBA to send the sheet as an email when a button is pressed.

Sub MailTest()
ActiveWorkbook.SendMail "email@email.com", "Leader Standard Work for previous week " & Date


End Sub

How would I change this to pull the email address from cell A10 on tab "Directions"?
I'm trying to make the sheet simpler for people to adjust who they want to email I to?

Thanks!

 

[Logit]

.
Try :

Option Explicit


Sub MailTest()
    ActiveWorkbook.SendMail Sheets("Directions").Range("A10").Value, "Leader Standard Work for previous week " & Date
End Sub

 

[blbat]

The following code is designed to pull from an email list created in Column A.

Note: the .Subject and .Body of the email must be changed to the verbiage you desire.

Option Explicit
-------------------------------------
Private Sub cmdTestEmail_Click()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim strCC As String
     
    Dim cell As Range
     
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    'OutApp.Session.Logon
     
    On Error GoTo cleanup
   For Each cell In ActiveWorkbook.Worksheets("Directions").Columns("A").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" Then
             
            If OutMail Is Nothing Then
                Set OutMail = OutApp.CreateItem(0)
                With OutMail
                    .To = cell.Value
                    .Subject = "CHANGE SUBJECT"
                    .Body = "Enter your Text here"
                     'You can add files also like this
                     '.Attachments.Add ("C:\test.txt")
                    .Display 'Or use Send
                End With
            Else
                On Error Resume Next
                strCC = strCC & cell.Value & ";"
                On Error GoTo 0
                 
            End If
             
        End If
    Next cell
    OutMail.BCC = Left(strCC, Len(strCC) - 1)
     
cleanup:
    Set OutMail = Nothing
    Set OutApp = Nothing
    Application.ScreenUpdating = True

End Sub

 

[BadFish523]

Logit,

That worked perfectly! Thank you for the quick reply and accurate response!

 

[BadFish523]

blbat,

Thank you for the response. I only have the one email and wont have the need for a list.

 

[Logit]

.
You are welcome. Glad to help.