Email Help

S

Stockwell43

Hello,

Here is the code I have behind my email button. So when a user is on a
record from there they click the button and the email will pull the info from
the selceted fields.

Private Sub cmdSendEmail_Click()
On Error GoTo EH
Dim SendTo As String, SendCC As String, MySubject As String, MyMessage
As String
SendTo = ""
SendCC = ""
MySubject = "Additional Information Needed"
MyMessage = vbCr & vbCr & Me.LoanNumber.Name & ": " & vbCr &
Me.LoanNumber & _
vbCr & Me.AddInfo.Name & ": " & vbCr & Me.AddInfo & _
vbCr & Me.StatusUpdate.Name & ": " & vbCr & Me.StatusUpdate
DoCmd.SendObject acSendNoObject, , , SendTo, SendCC, , MySubject,
MyMessage, True


EH:
If Err.Number = 2501 Then
MsgBox "This email message has not been sent. Message has been
cancelled."
End If
End Sub

The code works fine except the manager would like a few changes.

1. is there a way to make the Status Update field NOT appear if no comment
was made? In other words, if that field is blank on that record they don't
want the words Status Update to appear in the email.

2. How can I get this sentence:
Please provide the following on the above reference loan:
to appear in every email just above AddInfo?

Thanks!!!
 
S

Stockwell43

I was able to get number 2 to work so I only need to find out if number 1 is
doable.

Thank you!
 
S

Stockwell43

I'm sorry.

Is there a way to make the sentence in number 2 highlight in red everytime
the email opens?
 
K

Klatuu

For #1.
MyMessage = vbCr & vbCr & Me.LoanNumber.Name & ": " & vbCr &
Me.LoanNumber & _
vbCr & Me.AddInfo.Name & ": " & vbCr & Me.AddInfo

If Not IsNull(Me.StatusUpdate) Then
MyMessage = My Message & vbCr & Me.StatusUpdate.Name & _
": " & vbCr & Me.StatusUpdate
End If

As to your additional question, I don't know. You might try going to
www.Lebans.com and see if any of his utilities will do it.
 
C

Christopher Robin

I believe there is a way to get your sentence highlighted in Red, but it may
be more trouble than it's really worth, and I'm not certain how to do it all
in VB. I have a project set up that does send an HTML formatted email out,
but in order to format that email, I had to first query the DB to get the
data out in XML, and then run that XML through an XSL Transformation. In the
XSLT, I'm able to do many things with formatting, then the final HTML file is
read into a variable and sent via VB script.
 
S

Stockwell43

Hi Klatuu,

I not sure how to place this code. When I place it after this line of my code:
DoCmd.SendObject acSendNoObject, , , SendTo, SendCC, , MySubject, MyMessage,
True

The middle part:
MyMessage = My Message & vbCr & Me.StatusUpdate.Name & _
": " & vbCr & Me.StatusUpdate
Click to expand...

Is all red

If I place it after this part:
vbCr & Me.AddInfo.Name & ": " & vbCr & Me.AddInfo

All of the code turns red.

What am I doing wrong? How and where should I place it? I have tried many
different variations and if the code does not highlight red, the email still
contains Status Update:

Thanks!!
 
K

Klatuu

It may be because of the line continuation character at the end of this line:
MyMessage = My Message & vbCr & Me.StatusUpdate.Name & _
": " & vbCr & Me.StatusUpdate

If you have it all on one line, it should be:

MyMessage = My Message & vbCr & Me.StatusUpdate.Name & ": " & vbCr &
Me.StatusUpdate

It is just a syntax errror. If you can't work it out, post back the code as
it is now and I will see if I can find the problem.
 
S

Stockwell43

I definitely must be doing something wrong. I have this as one line:
MyMessage = My Message & vbCr & Me.StatusUpdate.Name & ": " & vbCr &
Me.StatusUpdate

and the whole thing comes up in red. Below is the code as I have it after
your last response.

Thanks Klatuu!

Private Sub cmdSendEmail_Click()
On Error GoTo EH

MyMessage = vbCr & vbCr & Me.LoanNumber.Name & ": " & vbCr &
Me.LoanNumber & _
vbCr & Me.CustLast & ", " & Me.CustFirst & _
vbCr & "Please provide the following on the above reference loan" & ": "
& vbCr & Me.AddInfo

If Not IsNull(Me.StatusUpdate) Then
MyMessage = My Message & vbCr & Me.StatusUpdate.Name & ": " & vbCr &
Me.StatusUpdate
End If

Dim SendTo As String, SendCC As String, MySubject As String, MyMessage
As String
SendTo = ""
SendCC = ""
MySubject = "Additional Information Needed"
MyMessage = vbCr & vbCr & Me.LoanNumber.Name & ": " & vbCr &
Me.LoanNumber & _
vbCr & Me.CustLast & ", " & Me.CustFirst & _
vbCr & "Please provide the following on the above reference loan" & ": "
& vbCr & Me.AddInfo & _
vbCr & Me.StatusUpdate.Name & ": " & vbCr & Me.StatusUpdate
DoCmd.SendObject acSendNoObject, , , SendTo, SendCC, , MySubject,
MyMessage, True


EH:
If Err.Number = 2501 Then
MsgBox "This email message has not been sent. Message has been
cancelled."
End If
End Sub
 
K

Klatuu

Sometimes things are so obvious we don't see them. I have been on both sides
of this conversation.
v-----Remove this space
MyMessage = My Message & vbCr & Me.StatusUpdate.Name & ": " & vbCr &
Me.StatusUpdate
 
S

Stockwell43

I'm sorry, I'm either blind or dense. Is there a "v" that has too much space
I need to omit? Or, do I need to omit the whole My Message= line?
 
S

Stockwell43

Ok, the red line is gone. But when I click the button, it gives my a Compile
Error:

"Variable not difined"

and it higlights in blue the very first beginning of the code:
MyMessage=
 
S

Stockwell43

I apologize, I forgot to take out the MyMessage = at the bottom of the code.

It works just fine now. Thanks again Klatuu and I am sorry for making this
out to more than it should have been. It's been a long week and trying get a
lot completed before the holiday.

Have a great weekend and 4th of July!!!
 
K

Klatuu

Thanks, you too.
I plan to walk over to the neighborhood park for a party and with any luck,
be carried home :)
 
S

Stockwell43

Sounds like my kind of party, have a couple for me!

I meant to ask you. Your nickname "Klatuu", two things come to mind:
The day the earth stood still and a band named Klatuu who some people
thought sounded like the Beatles. Does you name derive from either? Just
curious.
 
K

Klatuu

Never heard of the band.
The name is a misspelling from the movie. It is actually spelled Klaatu.
The movie is an all time favorite of mine.
I started using the name when waiting in line at restuarants because Dave is
such a common name. It sort of became a joke in the family and I began using
it for screen names on the internet.
 
S

Stockwell43

I can understand that. Mine is Doug but back in the day the called Chug for
reasons why you may need to be carried home tomorrow. :eek:)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Bold Code 2
Send email from access form 3
Email Issue 1
Email problem 5
BCC Email 3
Email issue 0
Emailing from a form 0
SendObject Email 2

Top