Display mutiple rows from a querydef in the body of an email created from Access03 (1 Viewer)

dkgilbert

New member
Local time
Today, 13:31
Joined
Mar 3, 2010
Messages
1
I am trying to create an outlook email using MS Access 2003. In the body of the email I am trying to print values from a a table or query. For example if I have a table that has 3 rows of data such as:

Field1 Field2 Field3
abc abc abc
ab2 ab2 ab2
123 123 123

I want to print the rows in the body of the text just as they appear above. I can create the email, but when I try to display the values, I can only get the first row to be displayed. I tried creating a querydef, opening the querydef as a recordset to get the values, then seting the values to a variable and using a do until eof statement. can soneone help?

Example of my code is listed below. Specifically I am trying to have the "ReserveTNinfo" variable display the rows from the "rs1" recordset which is set to the qdf1 querydef. I tried using a do until loop to change the values set to the "ReserveTNinfo" vairable to display each row in the querydef

Dim CustOrder, TTUEvent, BodyText1, BodyText2, QuestionsText, OMTeleconference, ReserveTNtext, ReserveTNinfo
Dim TTULocation, PreTestLocation
Dim SendCalendarInviteMsg, SendCalendarInviteStyle, SendCalendarInviteTitle, SendCalendarInviteResponse
Dim objOutlook As Outlook.Application
Dim outMail As Outlook.AppointmentItem
Dim db As Database
Set db = CurrentDb
Dim rs1 As Recordset
Dim qdf1 As QueryDef
Dim BVOIPval As String
Dim strTNQuery As String
Dim strSQLtn As String
strTNQuery = "qryTempTNinfo" & Me.Parent![OM_UID]
If fExistQuery(strTNQuery) Then
'DoCmd.DeleteObject acQuery, strTNQuery
End If
BVOIPval = Forms![frmordermanager]![BVOIP].Value
strSQL = "SELECT [TN Info].TN_From, [TN Info].TN_To, ([TN_To]-Right([TN_From],4))+1 AS TotalTNs FROM [TN Info]WHERE ((([TN Info].BVOIP)='" & BVOIPval & "') AND (([TN Info].[TN Type])='N'));"
Set qdf1 = db.CreateQueryDef(strTNQuery, strSQLtn)
Set rs1 = qdf1.OpenRecordset
SendCalendarInviteMsg = "Do you want to create an email calendar invite for this TTU event?" ' Define message.
SendCalendarInviteStyle = vbYesNo + vbDefaultButton1 ' Define buttons.
SendCalendarInviteTitle = "Send a Calendar Invite?" ' Define title.
SendCalendarInviteResponse = MsgBox(SendCalendarInviteMsg, SendCalendarInviteStyle, SendCalendarInviteTitle) 'Displays dialog box asking user if they want to send a calendar invite
If SendCalendarInviteResponse = vbYes Then ' User choses to send a calendar invite.
ReserveTNtext = vbCrLf & vbCrLf & "Your BVOIP voice services will be activated using the following new telephone numbers (TNs):" & vbCrLf & vbCrLf & " Reserved TNs" & vbCrLf & " FROM" & " TO " & " Total " & vbCrLf

ReserveTNinfo1 = rs1![TN_From] & " to " & rs1![TN_To] & " " & rs1![TotalTNs]
BodyText1 = "The " & Format(TTUEvent, "<") & " of your BVOIP Service has been confirmed and scheduled for " & Format(Me.TTU_Date, "dddd, mmmm d, yyyy") & " at " & Format(Me.TTU_Time, "h:mm AMPM") & " ET."
QuestionsText = "If you have any questions, please feel free to contact me."
If Me.TTU = "TTU-PreTest" Then
PreTestBodyText2 = " A field tech has been scheduled to install and test your BVOIP router. You will be notified 24 hours before the scheduled pre-test of the time that field tech will arrive at your location."
PreTestBodyText1 = "The " & Format(TTUEvent, "<") & " of your BVOIP Service has been confirmed and scheduled for " & Format(Me.TTU_Date, "dddd, mmmm d, yyyy") & "."
Set outMail = Outlook.CreateItem(olAppointmentItem)
outMail.Subject = "Confirmed " & Me.TTU_Date & " " & TTUEvent & " Of BVOIP Service For " & CustOrder
outMail.Location = PreTestLocation
outMail.MeetingStatus = olMeeting
outMail.Start = Me.TTU_Date & " 8:00 AM"
outMail.Duration = 540
Do Until rs.EOF
outMail.RequiredAttendees = rs! 'Me.Parent![SubFrmContacts].Form![email]
rs.MoveNext
Loop
outMail.Body = PreTestBodyText1 & PreTestBodyText2 & OMTeleconference
outMail.Display
ElseIf Me.TTU = "TTU-DV" Then
Dim TTUVP
TTUVP = " As we discussed, you are required to have your phone vendor and IT support onsite for the service activation. We have scheduled an onsite tech to install the BVOIP router. A Subject Matter Expert (SME) will be available via conference call to work with your phone vendor and IT support to successfully test your data and voice services. The dial information for the telephone conference call is as follows:"
Set outMail = Outlook.CreateItem(olAppointmentItem)
outMail.Subject = "Confirmed " & Me.TTU_Date & " " & TTUEvent & " Of BVOIP Service For " & CustOrder
outMail.Location = TTULocation
outMail.Start = Me.TTU_Date & " " & Me.TTU_Time
outMail.Duration = 120
outMail.RequiredAttendees = rs![email]
outMail.Body = BodyText1 & TTUVP & OMTeleconference & ReserveTNtext & ReserveTNinfo
outMail.Display
End If
Else
Exit Sub
End If
Exit Sub
 
Last edited:

vbaInet

AWF VIP
Local time
Today, 17:31
Joined
Jan 22, 2010
Messages
26,374
Maybe you want to show the code you use to perform your automation?
 

Users who are viewing this thread

Top Bottom