SendObject Function

[tma12000]

Can I use the email address listed in a record as the "To" field in this function. If so, what would the format in the "To" field look like? The table name is "user_info" and the filed is labeled "user_email".

Thanks,
Tim

 

[GingerR]

1. yes
2. check SEND OBJECT in HELP it has all the details

 

[tma12000]

Thanks Ginger,
Unfortunatley when I look thropugh help, it only says to put the recipient email address or leave it blank to be prompted for a recipient, it doesn't tell me how to reference the email addresses that are in the query output. Any other thoughts?

Thanks,
Tim

 

[MakeItSo]

hi Tim,

here's some code I use:

Function mail_bug()
On Error GoTo mail_bug_Err

    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Minimize
    DoCmd.OpenReport "Bericht_Massnahme", acPreview, "", "[ID]=[Forms]![Frm_NewBugs]![ID]"
    DoCmd.Maximize
    DoCmd.SendObject acReport, "Bericht_Massnahme", "RichTextFormat(*.rtf)", "", "", "", "Your subject", "", auto start (TRUE/FALSE), ""
    DoCmd.Close acReport, "Bericht_Massnahme"
    DoCmd.SelectObject acForm, "Frm_NewBugs", False
    DoCmd.Restore


mail_bug_Exit:
    Exit Function

mail_bug_Err:
    MsgBox Error$
    Resume mail_bug_Exit

End Function

Just adapt your report's name. Leave away the Minimize and Restore part if you wish - and have fun mailing... ;-)

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[GingerR]

did MakeItSo help?

if not--are what you saying is you have email addresses stored in a table, and you want them to be used in the TO: portion of your sendobject code?

ALL of the email addresses?

if so, you have to loop thru your table/query, string all of the email address together with semicolons, and plug that into the "TO" portion of the code string. if this is what you want to do but cannot do it, let me know and i'll send more info.

g

 

[tma12000]

Ginger, yes, the email addresses are stored in a table. I have a query that looks at service ticket#'s and their expiration date. When the exp date hits, I want to send a note to the email address stored in that particular record to the person who is responsible for the ticket. That means potentially I could have 5 tickets expire, and 5 mailnotes would have to be sent.

Thanks again!
Tim

 

[BuGlen]

Hi Tim,

This sample code may help as it looks like you're just wanting to send notification with no attachment. It uses the DAO recordset object, but it could easily be modified to use the ADO objects as well:

-----

Sub SendNotice()

    Dim rsTickets As DAO.Recordset
    Dim strTo As String, strSubject As String, strMessage As String
    Dim strSQL As String
    
    strSQL = "SELECT TicketNum, UserName, Email " & _
            "FROM tblTickets " & _
            "WHERE ([ExpDate] = #" & Date & "#);"
    
    Set rsTickets = CurrentDb.OpenRecordset(strSQL)
    If rsTickets.RecordCount = 0 Then Exit Sub
    
    With rsTickets
        Do While Not .EOF
            strTo = .Fields("Email").Value
            strSubject = "Ticket #" & .Fields("TicketNum").Value & "Notice"
            strMessage = "Dear " & .Fields("UserName").Value & _
                        vbCrLf & vbCrLf & _
                        "Your ticket #" & .Fields("TicketNum").Value & _
                        " is about to expire." & vbCrLf & vbCrLf & _
                        "Please be so kind as to stop surfing the web " & _
                        "and get back to work." & vbCrLf & vbCrLf & _
                        "Sincerly," & vbCrLf & _
                        "Management"
            DoCmd.SendObject To:=strTo, Subject:=strSubject, _
                MessageText:=strMessage
            .MoveNext
        Loop
    End With
    Set rsTickets = Nothing

End Sub

-----

Hope this helps.

Glen Appleton

VB.Net student.

 

[tma12000]

Glen thanks so much. You will have to forgive me... at best I am a decent Access user... My last quest is... where do I plop this code? In a module which would then be run out of autoexec?

Thanks again,
Tim

 

[BuGlen]

Hi Tim,

Yes, I would place the code in a module. If you want to be able to run the code from a Macro, you'll need to change it from a Sub() to a Function()

i.e. Function SendNotice()

Also change the "Exit Sub" statement in the code to "Exit Function".

Hope this helps.

Glen Appleton

VB.Net student.

 

[tma12000]

Glen,can the following line...
"WHERE ([ExpDate] = #" & Date & "#);"

Read as &quot;WHERE ([ExpDate] <= NOW

I need for this to work when the date has hit or passed. Is the NOW function valid in VB? If not, how do you think it should look?

Thanks SOOOOO much for your input.

Tim

 

[BuGlen]

Yes, the Now() and Date() functions are available in VBA so your logic should work fine. Just make sure you use the pound character to surround the date value in the query string. The pound character tells the query analyzer that you're evaluating a date expression:

&quot;WHERE([ExpDate] <= #&quot; & Date() & &quot;#);&quot;

Glen Appleton

VB.Net student.