By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,948 Members | 1,857 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,948 IT Pros & Developers. It's quick & easy.

Email report from Access as body of email not attachment

P: 33
I have a report that I currently email directly from Access as an attachment using a macro. I need to make it be the body of the email instead. Can that be accomplished without extensive VBA programming? Thanks!
Sep 7 '06 #1
Share this Question
Share on Google+
8 Replies


PEB
Expert 100+
P: 1,418
PEB
Unfortunatelly without Using VB scripts it seems a bit impossible!

First you need a vb script to transform your table, query or report in Message and

Second a VB script to send the message!

The Second one is easiest part of the task! we can help you with the second part!

For the first part what kind of information do you want to send and can you give us an exemple?
Sep 8 '06 #2

P: 33
The database is for help calls. The user will enter call information on a form, some fields are text boxes, some are combo fields. Some calls will have to be deferred to another user in the department so there is a command button to send and email and it is sending a report as attachment like this:
CallID: Date: Time: Clinic Number Caller Phone
4835 09/06/2006 12:48PM CBO John ext 1842

Subject: Category:
LSS Claims

Logged By: Status:
Jane Defferred

Question:
Need to send new ins claims thru standard EDI grp

************************************************** ******************************************
Each one of these are fields from the form. I have written VB code previously for emails so I know the basics of it, but can only seem to get one field per line of the email and we need multiple fields on a line. Thanks for the help, I need to take a class!


Unfortunatelly without Using VB scripts it seems a bit impossible!

First you need a vb script to transform your table, query or report in Message and

Second a VB script to send the message!

The Second one is easiest part of the task! we can help you with the second part!

For the first part what kind of information do you want to send and can you give us an exemple?
Sep 8 '06 #3

PEB
Expert 100+
P: 1,418
PEB
To do multiple Fields together:

My_message=Me!Field1+Space(10)+Me!Field2+Space(10) +Me!Field3+Space(10) +Chr(13)

My_message=My_message+ Me!Field11+Space(10)

The only thing that you have to check is verify that the fields aren't null.

So you obtain the message in your mail! In your script that sends the email you leave blank your attachement and you set your message to the variable that contains the information of your fields!

Space(10) leaves 10 empty positions

chr(13) makes a new line!
Sep 8 '06 #4

P: 33
You are wonderful! I knew there was a trick to it that I just hadn't figured out yet. I will give this a try. Thank You!

To do multiple Fields together:

My_message=Me!Field1+Space(10)+Me!Field2+Space(10) +Me!Field3+Space(10) +Chr(13)

My_message=My_message+ Me!Field11+Space(10)

The only thing that you have to check is verify that the fields aren't null.

So you obtain the message in your mail! In your script that sends the email you leave blank your attachement and you set your message to the variable that contains the information of your fields!

Space(10) leaves 10 empty positions

chr(13) makes a new line!
Sep 8 '06 #5

P: 33
I am getting a type mismatch when I click on the command to send the email, I think I've entered something wrong but am not sure. Here's what I have:

Private Sub Command122_Click()
On Error GoTo Err_Command122_Click

Dim stWhere As String
Dim stDocName As String
Dim stWho As String
Dim stCallid As String
Dim stDate As String
Dim stTime As String
Dim stSubject As String
Dim stContact As String
Dim stClinic As String
Dim stPhone As String
Dim stStatus As String
Dim stIssue As String
Dim stCtype As String
Dim stType As String
Dim stCategory As String
Dim stBy As String
Dim stResponse As String
Dim stText As String
Dim errLoop As Error

stWho = "email addresses" (I have real address on the form)
stCallid = Me.CallID

stSubject = "Deferred Help Call: " & stCallid

stDate = Me.Date
stTime = Me.Time
If Not IsNull(Me.Caller) Then stContact = Me.Caller
If Not IsNull(Me.Clinic_Number) Then stClinic = Me.Clinic_Number
If Not IsNull(Me.Phone) Then stPhone = Me.Phone
If Not IsNull(Me.Status) Then stStatus = Me.Status
If Not IsNull(Me.Question) Then stIssue = Me.Question
If Not IsNull(Me.ContactedBy) Then stCtype = Me.ContactedBy
If Not IsNull(Me.Subject) Then stType = Me.Subject
If Not IsNull(Me.Class) Then stCategory = Me.Class
If Not IsNull(Me.LoggedBy) Then stBy = Me.LoggedBy
If Not IsNull(Me.Response) Then stResponse = Me.Response


stText = stDate+Space(5)+stTime+Space(5)+stClinic+Space(5)+ stContact+Space(5)+stPhone+Chr(13)+Chr(13)
Space (5)+stType+Space(5)+stCategory+stCtype+Chr(13)+Chr (13)
Space (5) + stBy + Space(5) + stStatus + Chr(13) + Chr(13)
Space (5) + stIssue + Chr(13) + Chr(13)
Space (5) + stResponse + Chr(13)

DoCmd.SendObject , , acFormatTXT, stWho, , , stSubject, stText, -1

On Error GoTo Err_Execute
On Error GoTo 0

Exit Sub

Err_Execute:

If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If

Resume Next

Exit_Command122_Click:
Exit Sub

Err_Command122_Click:
MsgBox Err.Description
Resume Exit_Command122_Click

End Sub

To do multiple Fields together:

My_message=Me!Field1+Space(10)+Me!Field2+Space(10) +Me!Field3+Space(10) +Chr(13)

My_message=My_message+ Me!Field11+Space(10)

The only thing that you have to check is verify that the fields aren't null.

So you obtain the message in your mail! In your script that sends the email you leave blank your attachement and you set your message to the variable that contains the information of your fields!

Space(10) leaves 10 empty positions

chr(13) makes a new line!
Sep 8 '06 #6

PEB
Expert 100+
P: 1,418
PEB
Everything is on one line? if is it than type the line like:
stText = stDate+Space(5)+stTime+Space(5)+stClinic+Space(5)+ stContact+Space(5)+stPhone+Chr(13)+Chr(13) +
Space (5)+stType+Space(5)+stCategory+stCtype+Chr(13)+Chr (13) +
Space (5) + stBy + Space(5) + stStatus + Chr(13) + Chr(13) +
Space (5) + stIssue + Chr(13) + Chr(13) +
Space (5) + stResponse + Chr(13)

else

type

stText = stDate+Space(5)+stTime+Space(5)+stClinic+Space(5)+ stContact+Space(5)+stPhone+Chr(13)+Chr(13)
stText =stText + Space(5)+stType+Space(5)+stCategory+stCtype+Chr(13 )+Chr (13)
stText =stText + Space (5) + stBy + Space(5) + stStatus + Chr(13) + Chr(13) +
Space (5) + stIssue + Chr(13) + Chr(13)
stText =stText + Space (5) + stResponse + Chr(13)
Sep 8 '06 #7

PEB
Expert 100+
P: 1,418
PEB
The other things seem to be ok! :)
Sep 8 '06 #8

P: 33
The second one is it. It works great! Thank you.

Everything is on one line? if is it than type the line like:
stText = stDate+Space(5)+stTime+Space(5)+stClinic+Space(5)+ stContact+Space(5)+stPhone+Chr(13)+Chr(13) +
Space (5)+stType+Space(5)+stCategory+stCtype+Chr(13)+Chr (13) +
Space (5) + stBy + Space(5) + stStatus + Chr(13) + Chr(13) +
Space (5) + stIssue + Chr(13) + Chr(13) +
Space (5) + stResponse + Chr(13)

else

type

stText = stDate+Space(5)+stTime+Space(5)+stClinic+Space(5)+ stContact+Space(5)+stPhone+Chr(13)+Chr(13)
stText =stText + Space(5)+stType+Space(5)+stCategory+stCtype+Chr(13 )+Chr (13)
stText =stText + Space (5) + stBy + Space(5) + stStatus + Chr(13) + Chr(13) +
Space (5) + stIssue + Chr(13) + Chr(13)
stText =stText + Space (5) + stResponse + Chr(13)
Sep 8 '06 #9

Post your reply

Sign in to post your reply or Sign up for a free account.