How do I autofill the Subject line of an email with a cell value?

[Ruckus]

I have a multisheet workbook posted on the network which managers use to request ID's for new hires. I have it set up to send me an email when they hit a 'submit' button.

Currently it fills the subject line of the email with a static value that I've entered into the code. The problem is that I have dozens of these in my email and it's a mess trying to keep track.

I would like to have the subject line autofill with values (first and last name) entered into two different cells in the second worksheet.

I have been browsing the forums / google for a while now but am unable to find a solution that works.

Here is the what I'm currently using:



Set OutApp = CreateObject("Outlook.Application")
'On Error Resume Next
'OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = ""
.Body = "The form is attached."
.Attachments.Add wb2.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0

 

[borgem]

Have you tried adding in:

.Subject = Range("FN") & " " & Range("LN")

If that errors, you may have to abandon using the "With" portion of your code, but since it is short, that shouldn't be a huge problem.

 

[Ruckus]

I've tried that and the subject line comes back empty.. do I need to specify the sheet in the range?

.Subject = Range("'User Set Up'!C10") & " " & Range("'User Set Up'!C12") ?

 

[borgem]

I guess there is only one way to find out. Let me know how that works. I've done some similar applications, but not with email.

 

[Ruckus]

That's not working for me either

 

[borgem]

This is just a shot in the dark now, but maybe something like this will work:

Dim FN as String
Dim LN as String
FN = Range("'User Set Up'!C10").Value
LN = Range("'User Set Up'!C12").Value


and then : .Subject = FN & " " & LN

 

[blbat]

The Following code assumes you have standard email addresses listed in Column "A" of the Active Sheet. Cell A1 value is YOUR email address. It is written to be called from a command button on the Active Sheet. I hope it, or portions of it, can help you!

Private Sub cmdSendEmail_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")
 
    On Error GoTo cleanup
    For Each cell In 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 = "YOUR SUBJECT LINE HERE"
                    .Body = "TO Whoever, " & vbCrLf & vbCrLf & _
"Your text here with line breaks too....blah blah blah " & _
"blah blah blah  " & _
"blah blah blah " & _
"blah blah blah blah" & vbCrLf & vbCrLf & _
 & vbCrLf & vbCrLf & _
 
"Thank you." & vbCrLf & vbCrLf & _
"Sincerely," & vbCrLf & vbCrLf & _
"Your Name" & vbCrLf & _
"location" & vbCrLf & _
"your title" & vbCrLf & _
"your phone number: 857-6309"
 
                     'You can also add files like this:
                     '.Attachments.Add ("C:\whateverpath\test.txt")
 
 .Display 'Or use ".Send" But I don't because I want to visually check it first.
 
  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

I would love to take total credit for this...but I slapped it together with help from this site and Friends.

 

[borgem]

I just checked and it looks like my suggestion should work. According to this forum another format is:
.Subject = worksheets("'User Set Up'").range("C10")

I'm no expert, and I am known to be wrong quite often but, blbat, you offered unsolicited advice without even bothering to attempt to assist with the Ruckus' ACTUAL issue. I'm sure the help would be appreciated, but no in lieu of helping with the problem at hand. I'm not trying to be a jerk, but I'm just saying...

 

[blbat]

Sorry I have irritated you borgem..this was not my intent.

The code I offered does indeed use cell values to populate the Outlook message form...so I offered it with intent it could be modified to purpose.

I see that you are a board regular...and I am surprised at your attack.

I have lurked here for many years, and have learned a lot from modifying solutions that were presented here to meet my own needs.

I also am no expert, but my post WAS intended to help with the ACTUAL issue.

Thank you for your interest in my post.

v/r

blbat

 

[Ruckus]

Thank you both for your help!

borgem's solution worked after removing ' ' from the worksheet reference.

Here's what I ended up with:

.Subject = Worksheets("User Set Up").Range("C10") & " " & Worksheets("User Set Up").Range("C12")

Thanks again, you've made keeping track of these forms so much easier!