query to send email

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have determined from a query of a database table that our company is paying
for over 178 cellphones where the user has multiple cellphones. I already
have the list results but now I want to take everyone on the list and send
them an email confirming service and explaining why they need more than one
cellphone. The relevant table fields are PhoneNum, LastN, FirstN. Can I
programatically have a form email that will basically read something like -

Dear FName,

You have the following cellphones:

PhoneNum1
PhoneNum2
PhoneNum3
....

Why?

I know how to make a form letter, and I know that I need to establish some
kind of loop query to go through the data. I'm pretty much at a blank on how
to get an email to be sent out.

I have a kind of general idea, but it's not crystallizing for me, can anyone
help me?
 
This is from another post. This should give you an idea

Private Sub Command13_Click()

On Error GoTo Err_Command13_Click
Dim strBody As String
Dim rs As Object
Dim con As Object
Dim DateEnter As Date
Dim NoMeetings As Integer
Dim Meeting(100) As Integer
Dim i As Integer
Dim j As Integer
Dim room As String

i = 0
j = 0


If Not IsNull(Me![DateEnter]) Then

'this is an sql string to search by date. You'll need to change the name to
fit your database.
sqlst = "Select Distinct MeetingID " _
& "From MeetingData " _
& "WHERE ((MeetingData.MeetingDate) = #" & Me![DateEnter] & "#)"

Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.recordset")

rs.Open sqlst, con, 1

If Not rs.EOF Then

While Not rs.EOF

Meeting(i) = rs![MeetingID]
i = i + 1
rs.MoveNext
Wend

Else
MsgBox ("No meetings on this date") 'this is your msgbox for the user to
enter the date.
Exit Sub
End If


rs.Close

For j = 0 To i

'the following is a sql string that you will need to edit according to your
needs. Enter sql string
'after the sqlst=. There was a problem with carriage returns so leave
undercores in after each line along with ampersands

sqlst = "SELECT MeetingData.MeetingTitle, MeetingData.MeetingDate, " _
& "MeetingData.Description, MeetingData.SetupTime, " _
& "MeetingData.StartTime,MeetingData.EndTime, [Port-KivUsage].TimeID, " _
& "[Port-KivUsage].PortID,[Port-KivUsage].DialUpNo " _
& "FROM MeetingData Left JOIN [Port-KivUsage] ON " _
& "MeetingData.MeetingID=[Port-KivUsage].MeetingID " _
& "WHERE ((MeetingData.MeetingID) = " & Meeting(j) & ")"

'Set con = Application.CurrentProject.Connection
'Set rs = CreateObject("ADODB.recordset")

rs.Open sqlst, con, 1

If Not rs.EOF Then

strBody = strBody & "Port Assignments: " & Format(rs![MeetingDate],
"Long Date") & vbCr
strBody = strBody & vbCr
strBody = strBody &
"-------------------------------------------------------------" & vbCr
strBody = strBody & "Subject: " & rs![MeetingTitle] & vbCr
strBody = strBody &
"-------------------------------------------------------------" & vbCr
strBody = strBody & "Setup Time: " & Format(TimeSerial(3, 0, 0) +
rs![SetupTime], "Short Time") & " E " & Format(TimeSerial(2, 0, 0) +
rs![SetupTime], "Short Time") & " C " & Format(TimeSerial(1, 0, 0) +
rs![SetupTime], "Short Time") & " M " & Format(rs![SetupTime], "Short Time")
& " P " & vbCr
strBody = strBody & "Start Time: " & Format(TimeSerial(3, 0, 0) +
rs![StartTime], "Short Time") & " E " & Format(TimeSerial(2, 0, 0) +
rs![StartTime], "Short Time") & " C " & Format(TimeSerial(1, 0, 0) +
rs![StartTime], "Short Time") & " M " & Format(rs![StartTime], "Short Time")
& " P " & vbCr
strBody = strBody & "End Time: " & Format(TimeSerial(3, 0, 0) +
rs![EndTime], "Short Time") & " E " & Format(TimeSerial(2, 0, 0) +
rs![EndTime], "Short Time") & " C " & Format(TimeSerial(1, 0, 0) +
rs![EndTime], "Short Time") & " M " & Format(rs![EndTime], "Short Time") & "
P " & vbCr
strBody = strBody & "Description: " & rs![Description] & vbCr & vbCr
strBody = strBody & "Participants" & vbTab & "Port Number" & vbTab &
"Dial Number" & vbCr
While Not rs.EOF
If IsNull(rs![TimeID]) Then
room = ""
Else
room = DLookup("RoomName", "TimeCard", "[TimeID] = " &
rs![TimeID])
End If

strBody = strBody & room & vbTab & vbTab & rs![PortID] & vbTab &
rs![DialUpNo] & vbCr
rs.MoveNext
Wend
strBody = strBody & vbCr &
"********************************************************************************************" & vbCr

End If
rs.Close

Next j

Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.createitem(0)
myItem.Subject = "Subject" 'enter your email subject line here
myItem.Body = strBody
myItem.To = "(e-mail address removed)" 'enter your destination email here
myItem.Cc = ""
myItem.display

Set rs = Nothing

Else
MsgBox ("Please enter a date") 'this is your error msgbox if no date is
entered. you can change this message if you'd like
End If


Exit_Command13_Click:
Exit Sub

Err_Command13_Click:
MsgBox Err.Description
Resume Exit_Command13_Click

End Sub
 
OK, I studied your example and here's what I came to, deleting portions that
are just not relevant to my problem:

Private Sub Example()

Dim strBody As String
Dim rs As Object
Dim con As Object
Dim DateEnter As Date
Dim strEmployee As Integer
Dim i As Integer
Dim j As Integer
Dim room As String

i = 0
j = 0


If Not IsNull(Me![LAST NAME]) Then

Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.recordset")

rs.Open sqlst, con, 1

If Not rs.EOF Then

While Not rs.EOF

strEmployee(i) = rs![LAST NAME]

i = i + 1
rs.MoveNext
Wend

End If


rs.Close

For j = 0 To i

sqlst = "SELECT IIf(InStr([FIRST NAME]," ")=0,[FIRST NAME],Left([FIRST
NAME],InStr([FIRST NAME]," ")-1)) AS FNAME, morecells.[LAST NAME],
morecells.[PHONE NUMBER], IIf(InStr([FIRST NAME]," ")=0,[FIRST
NAME],Left([FIRST NAME],InStr([FIRST NAME]," ")-1)) & "." & [LAST NAME] &
"@MyCompany.net" AS Email
FROM morecells
GROUP BY IIf(InStr([FIRST NAME]," ")=0,[FIRST NAME],Left([FIRST
NAME],InStr([FIRST NAME]," ")-1)), morecells.[LAST NAME], morecells.[PHONE
NUMBER], IIf(InStr([FIRST NAME]," ")=0,[FIRST NAME],Left([FIRST
NAME],InStr([FIRST NAME]," ")-1)) & "." & [LAST NAME] & "@MyCompany.net"
HAVING (((morecells.[LAST NAME]) Not Like "Kroll"))
ORDER BY morecells.[LAST NAME]"

Next j

Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.createitem(0)
myItem.Subject = "Cell Phone Usage"

'This is where I want to create an email "template" that would go
'insert each individual employee's last name, as in Dear <<[LAST NAME]>>,
'and insert his/her phone numbers from my query,
'and send each employee an individualized email

'I'm not sure if this is necessary for me...or, is this where the specific
items
'I want in my "template" should have come from?
myItem.Body = strBody

'this would have to be in some sort of for...next loop
myItem.To = "(e-mail address removed)" 'enter your destination email here
myItem.Cc = ""
'i'm not sure what this is, but i don't need to display, if it just works
myItem.display

Set rs = Nothing

End Sub








schasteen said:
This is from another post. This should give you an idea

Private Sub Command13_Click()

On Error GoTo Err_Command13_Click
Dim strBody As String
Dim rs As Object
Dim con As Object
Dim DateEnter As Date
Dim NoMeetings As Integer
Dim Meeting(100) As Integer
Dim i As Integer
Dim j As Integer
Dim room As String

i = 0
j = 0


If Not IsNull(Me![DateEnter]) Then

'this is an sql string to search by date. You'll need to change the name to
fit your database.
sqlst = "Select Distinct MeetingID " _
& "From MeetingData " _
& "WHERE ((MeetingData.MeetingDate) = #" & Me![DateEnter] & "#)"

Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.recordset")

rs.Open sqlst, con, 1

If Not rs.EOF Then

While Not rs.EOF

Meeting(i) = rs![MeetingID]
i = i + 1
rs.MoveNext
Wend

Else
MsgBox ("No meetings on this date") 'this is your msgbox for the user to
enter the date.
Exit Sub
End If


rs.Close

For j = 0 To i

'the following is a sql string that you will need to edit according to your
needs. Enter sql string
'after the sqlst=. There was a problem with carriage returns so leave
undercores in after each line along with ampersands

sqlst = "SELECT MeetingData.MeetingTitle, MeetingData.MeetingDate, " _
& "MeetingData.Description, MeetingData.SetupTime, " _
& "MeetingData.StartTime,MeetingData.EndTime, [Port-KivUsage].TimeID, " _
& "[Port-KivUsage].PortID,[Port-KivUsage].DialUpNo " _
& "FROM MeetingData Left JOIN [Port-KivUsage] ON " _
& "MeetingData.MeetingID=[Port-KivUsage].MeetingID " _
& "WHERE ((MeetingData.MeetingID) = " & Meeting(j) & ")"

'Set con = Application.CurrentProject.Connection
'Set rs = CreateObject("ADODB.recordset")

rs.Open sqlst, con, 1

If Not rs.EOF Then

strBody = strBody & "Port Assignments: " & Format(rs![MeetingDate],
"Long Date") & vbCr
strBody = strBody & vbCr
strBody = strBody &
"-------------------------------------------------------------" & vbCr
strBody = strBody & "Subject: " & rs![MeetingTitle] & vbCr
strBody = strBody &
"-------------------------------------------------------------" & vbCr
strBody = strBody & "Setup Time: " & Format(TimeSerial(3, 0, 0) +
rs![SetupTime], "Short Time") & " E " & Format(TimeSerial(2, 0, 0) +
rs![SetupTime], "Short Time") & " C " & Format(TimeSerial(1, 0, 0) +
rs![SetupTime], "Short Time") & " M " & Format(rs![SetupTime], "Short Time")
& " P " & vbCr
strBody = strBody & "Start Time: " & Format(TimeSerial(3, 0, 0) +
rs![StartTime], "Short Time") & " E " & Format(TimeSerial(2, 0, 0) +
rs![StartTime], "Short Time") & " C " & Format(TimeSerial(1, 0, 0) +
rs![StartTime], "Short Time") & " M " & Format(rs![StartTime], "Short Time")
& " P " & vbCr
strBody = strBody & "End Time: " & Format(TimeSerial(3, 0, 0) +
rs![EndTime], "Short Time") & " E " & Format(TimeSerial(2, 0, 0) +
rs![EndTime], "Short Time") & " C " & Format(TimeSerial(1, 0, 0) +
rs![EndTime], "Short Time") & " M " & Format(rs![EndTime], "Short Time") & "
P " & vbCr
strBody = strBody & "Description: " & rs![Description] & vbCr & vbCr
strBody = strBody & "Participants" & vbTab & "Port Number" & vbTab &
"Dial Number" & vbCr
While Not rs.EOF
If IsNull(rs![TimeID]) Then
room = ""
Else
room = DLookup("RoomName", "TimeCard", "[TimeID] = " &
rs![TimeID])
End If

strBody = strBody & room & vbTab & vbTab & rs![PortID] & vbTab &
rs![DialUpNo] & vbCr
rs.MoveNext
Wend
strBody = strBody & vbCr &
"********************************************************************************************" & vbCr

End If
rs.Close

Next j

Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.createitem(0)
myItem.Subject = "Subject" 'enter your email subject line here
myItem.Body = strBody
myItem.To = "(e-mail address removed)" 'enter your destination email here
myItem.Cc = ""
myItem.display

Set rs = Nothing

Else
MsgBox ("Please enter a date") 'this is your error msgbox if no date is
entered. you can change this message if you'd like
End If


Exit_Command13_Click:
Exit Sub

Err_Command13_Click:
MsgBox Err.Description
Resume Exit_Command13_Click

End Sub


Ray S. said:
I have determined from a query of a database table that our company is paying
for over 178 cellphones where the user has multiple cellphones. I already
have the list results but now I want to take everyone on the list and send
them an email confirming service and explaining why they need more than one
cellphone. The relevant table fields are PhoneNum, LastN, FirstN. Can I
programatically have a form email that will basically read something like -

Dear FName,

You have the following cellphones:

PhoneNum1
PhoneNum2
PhoneNum3
...

Why?

I know how to make a form letter, and I know that I need to establish some
kind of loop query to go through the data. I'm pretty much at a blank on how
to get an email to be sent out.

I have a kind of general idea, but it's not crystallizing for me, can anyone
help me?
Click to expand...
Click to expand...
 
My sql statement works fine and gives me the grouping format that shows each
employee and his corresponding phone numbers on a separate page.

I'm trying to figure out how to automate or merge this information into an
individualized mailing. It's amazing to me that this is not easy to do. You
can't imagine how totally useful it would be for any large company to be able
to do this.
 
Back
Top