Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Coding for Email Subject Line 2

Status
Not open for further replies.

houstonbill

Technical User
Nov 6, 2006
92
Below you will see the coding that is used in my project to send an automated email with attachment, a subject line, and some test in the body of the email. Everything was working perfect until I had a request to add an additional field to my form (which is built from my table) and also add that field to the subject line.
The field "WorkOrderName" has been added to the table and any queries out there. It seems to be everywhere except on my VB drowndown when I type " stWorkOrderName = Format(Me.WorkOrderName)". Thus, it will not show up in my subject line. Does anyone know why this would be??


Private Sub btnEmail_Click()
On Error GoTo Err_btnEmail_Click


Dim stWhere As String '-- Criteria for DLookup
Dim varTo As Variant '-- Address for SendObject
Dim stText As String '-- E-mail text
Dim RecDate As Variant '-- Rec date for e-mail text
Dim stSubject As String '-- Subject line of e-mail
Dim stWho As String '-- Reference to tblUsers
Dim stDept As String '-- Department who assigned ticket
Dim stOrderID As String '-- Work Order Number
Dim stCorpDte As String '-- CorpRecv Date of Project
Dim stRequester As String '-- Person Requesting
Dim stProvName As String '-- Prov Office Requesting
Dim stWorkOrderName As String '-- Name Given to Work Order

'-- Combo of names to assign price change to
stWho = Me.cboGroup
stWhere = "ClientServices_tbl.GroupID = " & "'" & stWho & "'"


'-- Looks up email addresses from ClientServices_tbl
varTo = DLookup("[ClientEMAIL]", "ClientServices_tbl", stWhere)

RecDate = Me.CurrDte


stSubject = ":: New Work Order Request Submitted :: " & OrderID & Space(3) & ProvName & Space(3) & WorkOrderName

stOrderID = Format(Me.ID, "00000")
stProvName = Format(Me.ProvName)
stWorkOrderName = Format(Me.WorkOrderName)

'-- Evaluators employee who assigns ticket
stCorpDte = Me.CmpyRecvDte
stDept = Me.cboDepartment
stRequester = Me.ReqName

stText = "A new work order request has been submitted. Complete details can be located under the work order number on the database." & Chr$(13) & Chr$(13) & _
"Work Order Number: " & stOrderID & Chr$(13) & _
"Corporate received date: " & stCorpDte & Chr$(13) & _
"Requested by: " & stDept & Chr$(13) & _
"Sent by: " & stRequester & Chr$(13) & _
"This is an automated message. Please do not respond to this e-mail."


'Write the e-mail content for sending to assignee
DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1
DoCmd.GoToRecord , , acNewRec
Exit_btnEmail_Click:
Exit Sub

Err_btnEmail_Click:
MsgBox Err.Description
Resume Exit_btnEmail_Click

End Sub
 
if you step through the code what value do you get for stWorkOrderName?


ck1999
 
thanks for the link. There are tons of things out there but I'm not sure what I need is there. I will continue looking however, and trying to make this work.
 
Thanks folks, I have it correctly pulling my subject line the way it want it, with the particular fields populated.

Orginally this was set up as a Macro and everything was so easy. Now, due to the conversion I have 2 other questions related to VB entry that I have been playing with, trying different ways and cannot seem to make it work:
1) Within the text portion of my email if any of these fields happens to be null I get the message "invalid use of null" with no email. What coding or where do I indicate that if null, can still send. What I am using is not working. 2) I also need to incorporate the attachment of a report in Snapshot mode. I've tried the "send object" below in multiple formats but nothing is working.

Dim stDocName As String
stDocName = "rptCoverSheet"
DoCmd.SendObject acReport, stDocName






 
1) Use the Nz function
2) DoCmd.SendObject acSendReport, stDocName, acFormatSNP

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
got the NZ function to go. That is what I was doing before but had a bit of a typo.

Not to be a pain, but using the code for attaching the report is a bit tricky. I Understand it and will save for further use but I am not quite sure where to put it. I assumed it should go immediately following all the "Dims" and although it does attach my report, the balance of my email formatting is lost. By locating it within or after the email coding, all the text is formated for the email, but the doc does not attach. Also includedit in the Dim definitions, with the DoCmd included with the other DoCmd's but still no luck What would be the proper location?
Dim stDocName As String
stDocName = "rptCoverSheet"
DoCmd.SendObject acSendReport, stDocName, acFormatSNP

 
I guess you should replace this:
DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1
with this:
DoCmd.SendObject acSendReport, stDocName, acFormatSNP, varTo, , , stSubject, stText, True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
That did it! You can bet I will hang on to this for future reference. I do appreciate the time you have taken with me. I am by no means an expert in Access and continue to learn by trying things that I have never done before.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top