sending email based off of email in a cell

[aragon123321]

Is there any way to put a email into a cell on a workbook and then use a macro to send a email using that cell.

Here is what I am sending a email with now: and it works

Private Const ¦¦DefaultMessageSender¦¦ = "name "
Private Const ¦¦DefaultMessageSenderEmail¦¦ = "name@work.com"
Private Const ¦¦DefaultMessageRecipient¦¦ = "phone@txt.att.net"





Sub Step2(Optional ¨¨Message As String = "", Optional ¨¨Recipient As String = ¦¦DefaultMessageRecipient¦¦, Optional ¨¨Project As String = "Current Function")
Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
Dim Flds As Variant


Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")


iConf.Load -1 ' CDO Source Defaults
Set Flds = iConf.Fields
With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "unixmailhost.work.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Update
End With

'If ((InStr(1, ¨¨Project, "Function") <> 0) And (InStr(1, ¨¨Project, "Subroutine") <> 0)) Then ¨¨Project = ¨¨Project & "() Function"
If (¨¨Message = "") Then ¨¨Message = "Excel has finished processing your " & ¨¨Project & " in " & Q(GetActiveWorkbookName())
strbody = ¨¨Message



Set iMsg.Configuration = iConf
iMsg.To = ¨¨Recipient
iMsg.CC = ""
iMsg.BCC = ""
If (SubStr(¨¨Recipient, -22, 22) = "@pager.nnn.work.com") Then iMsg.From = " Excel Alert" Else iMsg.From = """" & ¦¦DefaultMessageSender¦¦ & """ <" & ¦¦DefaultMessageSenderEmail¦¦ & ">"
iMsg.Sender = ¦¦DefaultMessageSenderEmail¦¦
iMsg.Subject = "Excel Alert"
iMsg.TextBody = " Look at Status Monitoring Report for Errors " & strbody
iMsg.Send


End Sub

I would like to not have
Private Const ¦¦DefaultMessageSender¦¦ = "name "
Private Const ¦¦DefaultMessageSenderEmail¦¦ = "name@work.com"
Private Const ¦¦DefaultMessageRecipient¦¦ = "phone@txt.att.net"

In macro and would rather just use a email that was in a cell.
Is this possible? i have tried a lot of different things without any success.

 

[ImranMalek]

Hi,
Considering you have sender's email address in cell A1 following would work

Declare your constants like this
Public Const DefaultMessageSenderEmail As String = "$A$1"

and then in your sub routine use them as following

Sub Step2()


    Msg.Sender = DefaultMessageSenderEmail
    Dim msgsender As String: msgsender = Range(DefaultMessageSenderEmail).Value


End Sub

Hope it helps!