Excel ActiveWorkbook.SendMail Dynamic Possibilities? 1

[Lunatic]

In Excel I'm working on a macro that uses the ActiveWorkbook.SendMail feature and having some trouble finding the answers I'm looking for in the help file or in the archives.

*****

Question 1) Using the basic ActiveWorkbook.SendMail can you send the e-mail to different people depending on the input?

For Example if "11" is entered in Cell B3 is there a way to send the message to Joe Bob and Billy Bob, but if "21" is entered Send it to Jane Doe and John Doe?

I'm probably not looking for the correct phrase when I search...

*****

Question 2) Probably very similar answer to question 1. How do you make the subject line dynamic?

For Example: Thank your for XYZ where XYZ is the value of Cell A22?

*****

Any tips, hints, or points in a good direction are much appriciated. Thanks!

***************************************
Have a problem with my spelling or grammar? Please refer all complaints to my English teacher:
Ralphy "Me fail English? That's unpossible." Wiggum

 

[PHV]

What is your actual code ?
FYI, in your code place the cursor inside the SendMail word and hit the F1 key.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Lunatic]

Private Sub CommandButton1_Click()

If Worksheets("Ad hoc").Range("B18") = IsBlank Or Worksheets("Ad hoc").Range("C16") = IsBlank Or Worksheets("Ad hoc").Range("I16") = IsBlank Or Worksheets("Ad hoc").Range("E18") = IsBlank Or Worksheets("Ad hoc").Range("I18") = IsBlank Then
    MsgBox ("Please enter in your name, the date, and locality, section, and unit information before proceeding.")
Else
    ActiveWorkbook.SendMail Recipients:="JohnDoe@fake.com"
    MsgBox ("Your file has been sent or is waiting in your outbox to be sent.")
End If
End Sub

1) There are 20 localities. What I am hoping is that there is a way to set it up so the e-mail goes to the manager and director, who vary depending on the locality.
Essentially, if B18 = 1 then e-mail to X and Y. If A22 = 12 then e=mail to P and Q.

2) Ideally, in A23 the user enters some information that I would like to be included in the subject line. So part of the subject line would be pre-set and part would be dependent on the user entry.

Thanks for the F1 key quick help shortcut. That will help with numerous things in the future.

The help files show you how to make static e-mails, and I made them to start with. Now I want to make them dynamic, based on user imput.

Thanks!

***************************************
Have a problem with my spelling or grammar? Please refer all complaints to my English teacher:
Ralphy "Me fail English? That's unpossible." Wiggum

 

[RoyLatham]

To change the subject, use the Subject parameter.

ActiveWorkbook.SendMail Recipients:="JohnDoe@fake.com", subject:="My subject line"

ActiveWorkbook.SendMail _
Recipients:="JohnDoe@fake.com", _
subject:="Fixed part of subject line: " & Worksheets("Ad hoc").Range("A23").value

Roy

 

[Lunatic]

Roy,

Thank you! Subject line is now dependent on user entry.

I think I have a way of making the e-mail address dynamic as well. I'll post my attempt after I try.

Thanks again!

***************************************
Have a problem with my spelling or grammar? Please refer all complaints to my English teacher:
Ralphy "Me fail English? That's unpossible." Wiggum

 

[Lunatic]

Okay, with a little help from PHV from this thread:

http://www.tek-tips.com/viewthread.cfm?qid=847931

(another star there for you), the code seems to work. Here it is:

Private Sub CommandButton1_Click()

Dim myArr(2) As String
myArr(0) = "fake@fake.com"
myArr(1) = Worksheets("Ad hoc").Range("R47").Value
myArr(2) = Worksheets("Ad hoc").Range("T47").Value

If Worksheets("Ad hoc").Range("B18") = IsBlank Or Worksheets("Ad hoc").Range("C16") = IsBlank Or Worksheets("Ad hoc").Range("I16") = IsBlank Or Worksheets("Ad hoc").Range("E18") = IsBlank Or Worksheets("Ad hoc").Range("I18") = IsBlank Then
    MsgBox ("Please enter in A, B, and C, D, and E information before proceeding.")
Else
    ActiveWorkbook.SendMail myArr, _
    Subject:="Ad-Hoc Issue for: " & Worksheets("Ad hoc").Range("A22").Value
    MsgBox ("Your file has been sent or is waiting in your outbox to be sent.")

End If
End Sub

The subject and 2 of the 3 e-mail recipients are dependent on user input with 1 e-mail set as a constant.

The last hurdle I can see is trying to define what R47 and T47 are equal to.

What I would like is for them to be equal to the non-z value in the range of R25:R44 and T25:T44. At most, only 1 value in that range will be equal to something other than Z.

I can't find a formula that will check that range and throw out all the z's and keep anything =/= z.

Formula help?

Thank you for all your help so far!

***************************************
Have a problem with my spelling or grammar? Please refer all complaints to my English teacher:
Ralphy "Me fail English? That's unpossible." Wiggum