Outlook email thru access

vbbbmom

Registered User.
Local time
Today, 14:31
Joined
Jul 12, 2011
Messages
33
The code I have works great as long as the 2 CC fields are not null. Can someone tell me how to make it work if the fields are null? :confused: Here is my code:

Private Sub cmdMailTicket_Click()
On Error GoTo Err_cmdMailTicket_Click
Dim varTo As Variant '-- Address for SendObject
Dim stCC As String '--Addresses for CC
Dim stText As String '-- E-mail text
Dim RecDate As Variant '-- Rec date for e-mail text
Dim JobNum As Variant '-- Job Number for subj line text
Dim stSubject As String '-- Subject line of e-mail
Dim strSQL As String '-- Create SQL update statement
Dim errLoop As Error

'-- Combo of names to Send Note to
varTo = Me.strEmailAddress
stCC = Me.strCCEmailAddress & "; " & Me.strCC2EmailAddress

stSubject = Me.[Job Number] & " - " & Me.strSubjectLine

stText = Me.strComments

'Write the e-mail content for sending to assignee
DoCmd.SendObject , , acFormatTXT, varTo, stCC, , stSubject, stText, -1
 
Hi

This can really be just string manipulation, rather than having much bearing on your Outlook code.
For example:
stCC = Nz(Me.strCCEmailAddress, "") & ("; " + Me.strCC2EmailAddress)

To ensure you have a zero length string (as you're using a string variable not a variant) and don't need the superflous semi colon unless there is a second value.

Cheers
 
Thank You! That worked perfectly!
 
Welcome. You're OK with the how and why too? (A bit brief of late, through necessity :-)
 
Welcome. You're OK with the how and why too? (A bit brief of late, through necessity :-)

Yes I am. I do have another question related to this that maybe you can help me with.

I now have a subject table that includes and ID#, a Subject Line, and a pre written message related to the subject. I have set the after update of the subject to automatically fill the strComments box (a memo box) with the message that is related to that subject line. What I need to know now:

Is there a way to merge data into that pre-populated comments box as well? The data would be based off of information from my form. For example, I am sending a client an appt. reminder, so I will choose appt. reminder in my subject line drop down. My pre-written text appears that looks like this:

Hello!

This is a reminder that you have an installation appt. scheduled for:

Start Date: XXXXXXXXX

Arrival time: XXXXXXXXX

Please contact me if you need to reschedule this appt. I can be reached at:

My Info is here which is already in the prewritten text.

Basically I woud like the X's next to the start date and arrival time to be like merge fields and that data be found from a box on my form and automatically populate within the pre-written text. Because that particular information is different for each client I cannot just include it in the pre-written text.

Hopefully I have explained this to where you will understand it. Oh, I am not emailing an attachement, the message is directly placed in the body of the email. I do not want to use attachements for this.

Please let me know if you can help with this, or if it is even possible.
 
Generally text substitution (which is fairly common conceptually) is facilitated by escaping your text fields. This identifies the fields in text as being for substitution (as opposed to just being text which happens to match your field name for example.

Hello!

This is a reminder that you have an installation appt. scheduled for:

Start Date: <<StartDate>>

Arrival time: <<ArrivalTime>>

Please contact me if you need to reschedule this appt. I can be reached at:


That way you can perform a replace on your prepared text for each of your field names. Or, if you have a prohibitively large number of field names - scour the text for occurences of the field escape characters and find the field name to replace.
(The former is more simple.)
If, as you mention, this was just a form - you could have something vaguely like:

Code:
Dim strText As String
Dim fld As DAO.Field
 
strText = Me.SubjectControl.Column(2) 'Or wherever you get it from.
For Each fld In Me.Recordset.Fields
    strText = Replace(strText, "<<" & fld.Name & ">>", fld.Value)
Next
Me.Comments  = strText
 

Users who are viewing this thread

Back
Top Bottom