Code for emailing - problem.

[sha7jpm]

two queries really.

1. i have code attached to this posting which emails data to a user. this all works fine no problems at all.
but the email sends anything from 1 record to 10.
does anyone know of syntax to put in that will make the email only pick up records that are not blank? I tried an if then else statement but the coding fell over.

at present I have titles for 10 records but obviously if the user only selects 2 records to email the others stay empty, which makes the email slightly cumbersome.

2. also does anyone know the syntax for making the text bold? so the resulting email will have bold fonts?

many thanks

John

 

[Tim K.]

Try the function below.

Function fIsBlank(strField As String, I As Integer, _
Optional strString2 As String) As String
Dim strR As String

If strField <> "" Or Not IsNull(strField) Then
Select Case I
Case Is = 1
strR = "Instrument Description : -" & " " & strField & strString2
Case Is = 2
strR = "SNAP FileName : -" & " " & strField & strString2
Case Is = 3
strR = "Instrument Description : -" & " " & strField & strString2
End Select
Else
strR = ""
End If

fIsBlank = strR

End Function

Then change your code like showing below.

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click
Dim strTextMessage As String
strTextMessage = "The Following Instruments have now been completed for" & Chr(13)
strTextMessage = strTextMessage & Chr(13)
strTextMessage = strTextMessage & Me!txt1a & " - " & Me!txt1d & Chr(13)
strTextMessage = strTextMessage & Chr(13)
strTextMessage = strTextMessage & fIsBlank(Me!txt1b, 1, Chr(13))
strTextMessage = strTextMessage & fIsBlank(Me!txt1b, 2, Chr(13))
strTextMessage = strTextMessage & fIsBlank(Me!txt1b, 3, Chr(13))
strTextMessage = strTextMessage & Chr(13)
...

For your second question, I think you have to use OutLook to apply rich text format in your email, just check the at http://www.calvinsmithsoftware.com/cserve.htm to find an example code for send email vie OutLook.

 

[sha7jpm]

many many thanks!

cheers for all your help,

I'll give the syntax a go..

very kind of you to help.

cheers

John

 

[Drevlin]

OK someone else answered your question before I got to it... But I'm going to post this anyway just to give you a different idea on how to do this. I'm going to go under the assumption that if the txt*b is empty then txt*c and txt*f is empty. I would try something like this:

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click
Dim y as Long
Dim strTextMessage As String

strTextMessage = "The Following Instruments have now been completed for" & Chr(13)
strTextMessage = strTextMessage & Chr(13)
strTextMessage = strTextMessage & Me!txt1a & " - " & Me!txt1d & Chr(13)
strTextMessage = strTextMessage & Chr(13)

For y = 1 to 10

If Not IsEmpty(Eval("Forms!ThisForm.txt" & y & "b")) Then

strTextMessage = strTextMessage & "Instrument Description : -" & " " & Eval("Forms!ThisForm.txt" & y & "b") & Chr(13)
strTextMessage = strTextMessage & "SNAP FileName : -" & " " & Eval("Forms!ThisForm.txt" & y & "c") & Chr(13)
strTextMessage = strTextMessage & "Number of Records : -" & " " & Eval("Forms!ThisForm.txt" & y & "f") & Chr(13)
strTextMessage = strTextMessage & Chr(13)

End If

Next y


DoCmd.SendObject acSendNoObject, , , Me!txtemailaddressCC, Me!rds1, , "Project Code " & Me!txt1a, strTextMessage, True

Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click

End Sub
Private Sub textline2()

End Sub

Private Sub Text26_BeforeUpdate(Cancel As Integer)

End Sub

You, of course, have to change "ThisForm" to the name of your Form (Eval("Me.txt1b") doesn't work.)

I haven't tested this code, but the only part I think you would have a problem with would be the: If Not IsEmpty()
You may have to change it to IsNull() or to be safe do an or statement for both.

Anyway, the basic idea was to introduce you to Eval() and show you how you can use it to shorten your code (and of course answer your question).

Peace

 

[sha7jpm]

Null problem

Tim I have run your code, and all seemed fine until it called the field blank routine, and I had a invalid use of Null error occur.

my text boxes are a variety of different data types

would that cause the problem?