0

I'm currently trying to add a name from a form onto an email on Outlook. However I can only capture from one worksheet which works successfully:

 .Subject = "New Form Name: - " & Worksheets("Form1").Range("C50")

However I want the code to be able to check if another worksheet in a different field has been filled out and to use the one which had data in it.

I've tried this (using Or), but with no luck:

.Subject = "New Form Name - " & Worksheets("Form1").Range("C50") Or Worksheets("Form2").Range("A25") Or Worksheets("Form3").Range("B19")

The above goes not generate anything in the Subject line of the email.

Is there a way for it to show which has data in one of the worksheets and to capture this.

Any help would be great!

Community
  • 1
  • 1
user1832421
  • 21
  • 1
  • 5
  • Iterate through all worksheets first, then decide whether you want to add a subject. Check http://stackoverflow.com/questions/10654797/how-can-i-use-vba-to-list-all-worksheets-in-a-workbook-within-a-string – LinuxDisciple Mar 21 '16 at 19:55

1 Answers1

0

Assuming you want to check in the order you wrote use this:

Dim sName as String

sName = Worksheets("Form1").Range("C50") 
If sName = "" Then sName = Worksheets("Form2").Range("A25")
If sName = "" Then sName = Worksheets("Form3").Range("B19")

.Subject = "New Form Name - " & sName

This will show a blank for sName if none of the three have a name and will look in the certain order, but it's enough to play with to meet your exact needs.

Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72