Try this. Would should see how this code is making the text string and
change it to meet what you want. Also, I noticed you used the word Date as a
field name in your table. You should change this since Date is a reserved
word in access and can cause problems. I would suggest something like
MeetingDate instead. I have not tested, so let me know if there are any
problems.
Private Sub Command5_Click()
On Error GoTo Err_Command5_Click
Dim strBody As String
Dim rs As Object
Dim con As Object
Dim DateEnter As Date
DateEnter = InputBox("Please Enter a Date (MM/DD/YYYY)")
sqlst = "SELECT MeetingData.MeetingTitle, MeetingData.Date,
MeetingData.Description, MeetingData.SetupTime,
MeetingData.StartTime,MeetingData.EndTime, [Port-KivUsage].TimeID,
[Port-KivUsage].PortID,[Port-KivUsage].DialUpNo;" _
& "FROM MeetingData INNER JOIN [Port-KivUsage] ON
MeetingData.MeetingID=[Port-KivUsage].MeetingID" _
& "WHERE (((MeetingData.Date) = " & DateEnter & ")"
Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.recordset")
rs.Open sqlst, con, 1
If Not rs.EOF Then
strBody = "Meeting: " & rs![MeetingTitle] & vbCr
strBody = strBody & "Date: " & rs![Date] & vbCr
strBody = strBody & "Setup Time: " & rs![SetupTime] & vbCr
strBody = strBody & "Date: " & rs![Date] & vbCr
strBody = strBody & "Start Time: " & rs![StartTime] & vbCr
strBody = strBody & "End Time: " & rs![EndTime] & vbCr
strBody = strBody & "Port: " & rs![PortID] & vbCr
strBody = strBody & "Dial up Number: " & rs![DialUpNo] & vbCr
strBody = strBody & "Description: " & rs![Description]
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.createitem(0)
myItem.Subject = "Port Assignments"
myItem.Body = strBody
myItem.To = "(e-mail address removed)"
myItem.Cc = ""
myItem.display
Else
MsgBox ("No matching meetings")
End If
rs.Close
Set rs = Nothing
Exit_Command5_Click:
Exit Sub
Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click
End Sub
Beyuduzz said:
Are you referring to an SQL string. I inserted the SQL string that generates
the report, but it obviously doesn't work. Am I moving in the right
direction?
Private Sub Command5_Click()
Private Sub Command5_Click()
On Error GoTo Err_Command5_Click
Dim strBody As String
strBody = "Date: " & Me![ReportDate] & vbCr
strBody = strBody & "SELECT MeetingData.MeetingTitle, MeetingData.Date,
MeetingData.Description, MeetingData.SetupTime, MeetingData.StartTime,
MeetingData.EndTime, [Port-KivUsage].TimeID, [Port-KivUsage].PortID,
[Port-KivUsage].DialUpNo;" _
& "FROM MeetingData INNER JOIN [Port-KivUsage] ON
MeetingData.MeetingID=[Port-KivUsage].MeetingID" _
& "WHERE (((MeetingData.Date) Like " * " & [Enter Date
MM/DD/YYYY ] & " * ")" & _
Me![Value]
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.createitem(0)
myItem.Subject = "Port Assignments"
myItem.Body = strBody
myItem.To = "(e-mail address removed)"
myItem.Cc = ""
myItem.display
Exit_Command5_Click:
Exit Sub
Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click
End Sub
--
I''m a novice with an advance way of thinking.
:
I was just making up values from a make believe form to show how to construct
a string to put in the body of the email. I do not know enough about your
system to be able to give you exactly what you would need so I am giving
general examples. The key is you will have to generate a string expression
to insert into the body of the email.
:
I'm sorry, I have no idea what that means. Maybe I'm just in over my head.
I tried to find something to insert into the "The value of whatever you where
looking for is: " and a value to put into the me![value], but I can't seem to
find anything. I usually can figure it out, but i'm completly lost here.
--
I''m a novice with an advance way of thinking.
:
You then need to construct a text sting and set the body equal to that. For
example if the report is based on a current record on a form that is open:
Private Sub Command5_Click()
On Error GoTo Err_Command5_Click
Dim strBody as string
strBody = "Date: " & Me![ReportDate] & vbCR
strBody = strbody & "The value of whatever you where looking for is: " &
Me![value]
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.createitem(0)
myItem.Subject = "Port Assignments"
myItem.Body = strBody
myItem.To = "(e-mail address removed)"
myItem.Cc = ""
myItem.display
Exit_Command5_Click:
Exit Sub
Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click
This is one way to construct the string. There are others depending on your
conditions.
:
Ok. Thanks. I actually want a simple all text report to be the body of the
email. My users hate attachments.
--I''m a novice with an advance way of thinking.
:
You need to remove these two lines:
stDocName = "Port Assignments"
DoCmd.SendObject acReport, stDocName
Then you need to insert a string expression of what you want the body of the
email to say. Or delete that line and it will pull up a blank email.
:
Its actually not a runtime error. Sorry. Its a generic compile error
"expected expression after the myItem.Body =
Here is the code with your updates. Access is also still trying to attach
the report to the email. I can't seem to find anything on what to put in
the
"myItem.Body=" I assume I need a reference to point to the report name.
Private Sub Command5_Click()
On Error GoTo Err_Command5_Click
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.createitem(0)
myItem.Subject = "Port Assignments"
myItem.Body =
myItem.To = "(e-mail address removed)"
myItem.Cc = ""
myItem.display
stDocName = "Port Assignments"
DoCmd.SendObject acReport, stDocName
Exit_Command5_Click:
Exit Sub
Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click
End Sub
--
I''m a novice with an advance way of thinking.
:
What does the error say? What does your code look like now?
:
Ok. I replaced the DimstDocName As String with your code. I am now getting a
runtime error on the "myitem.body=" line. I don't know what to make that
equal I expereimented with some things, but nothing....Thannk for the help.
Tom
--
I''m a novice with an advance way of thinking.
:
Replace
Dim stDocName As String
stDocName = "Port Assignments"
DoCmd.SendObject acReport, stDocName
With the other code. You will have to create a large string of data to put
in the body.
:
Where do I place this code in the VBA window.
--
I''m a novice with an advance way of thinking.
:
Instead of sendobject you can automate outlook
Set myOlApp = CreateObject("Outlook.Application")
Set myitem = myOlApp.createitem(0)
myitem.Subject = 'Put the subject here
myitem.body = 'build information you want in the body and insert it here
myitem.To = 'string of how to send to
myitem.Cc = 'string of how to copy
myitem.display
:
This has been asked in three other posts, but no quite answered. I have an
all text report that I would like to email to a Group list in Outlook 2003,
but I don't want to send it as an attachment. The format doesn't matter,
just as long as the text of the report is in the BODY of the email. I would
also like to automate the TO: and SUBJECT: lines. here is what I have so
far...
---------------------------------------------------------------------
Private Sub SndPrtAssns_Click()
On Error GoTo Err_SndPrtAssns_Click
Dim stDocName As String
stDocName = "Port Assignments"
DoCmd.SendObject acReport, stDocName
Exit_SndPrtAssns_Click:
Exit Sub
Err_SndPrtAssns_Click:
MsgBox Err.Description
