Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

query to send email

Discussion in 'Information Technology' started by Ray S., Jul 28, 2006.

Tags:
  1. Ray S.

    Ray S.
    Expand Collapse
    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?
     
  2. Loading...

    Similar Threads Forum Date
    Query about Jhatka Meat by Shooting in Head Sikh Sikhi Sikhism Aug 26, 2011
    Who is a sikh? A non sikh friend's query!! Sikh Sikhi Sikhism Apr 30, 2010
    General Query Hard Talk Sep 4, 2008
    Power of pauri's in Japji Sahib query Sikh Sikhi Sikhism Aug 17, 2006
    Sikhism a query Book Reviews & Editorials Aug 2, 2005

  3. schasteen

    schasteen
    Expand Collapse
    Guest

    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 = "email@domain.com" '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." wrote:

    > 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?
     
  4. Ray S.

    Ray S.
    Expand Collapse
    Guest

    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 = "email@domain.com" '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" wrote:

    > 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 = "email@domain.com" '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." wrote:
    >
    > > 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?
     
  5. Ray S.

    Ray S.
    Expand Collapse
    Guest

    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.
     

Share This Page