Send email button

[rlmorgan]

Hi all, I am have trouble with a command button code. I have a button that sends mail merged emails out based on a query. This is working great but I can’t figure out how to change a yes/no field in the query that flags it as sent. There can be anywhere from 1 to 40 emails the can be sent out with the command button. Can I change the flag value on all the records found in the query using the same On Click code?

Private Sub cmdTicketSend_Click()
On Error GoTo Err_cmdTicketSend_Click

    Application.FollowHyperlink ("c:\Docs\Ticket.doc")

Exit_cmdTicketSend_Click:
    Exit Sub

Err_cmdTicketSend_Click:
    MsgBox Err.Description
    Resume Exit_cmdTicketSend_Click
End Sub

 

[Remou]

Probably. What is the SQL for the query? Is it a saved query?

 

[rlmorgan]

I am noy sure what a saved query is. If you mean will it update the underlaying table, yes it will.

Here is the SQL for the query:

SELECT tbl2006Full.AssetTag, tbl2006Full.ServiceTag, tbl2006Full.ComputerType, tbl2006Full.Model, tblAUsers.LastName, tblAUsers.FirstName, Right([Phone],Len([Phone])-3) AS [Phone#], tbl2006Full.EMail, tbl2006Full.NetID, tblAUsers.Mailstop, tblAUsers.Dept, tblAUsers.CostCenter, tblAUsers.Site, tblAUsers.Building, tbl2006Refresh.RefreshDate, tbl2006Refresh.Location, tbl2006Refresh.[User Comments], "Someone@Overthere.com" AS MailTo, "Someone@Anywhere.com" AS MailCC, tbl2006Refresh.TicketOpenedDate
FROM (tbl2006Refresh INNER JOIN tbl2006Full ON tbl2006Refresh.AssetTag = tbl2006Full.AssetTag) INNER JOIN tblAUsers ON tbl2006Full.NetID = tblAUsers.NetID
WHERE (((tbl2006Refresh.RefreshDate)<(Date()+18)) AND ((tbl2006Refresh.TicketOpenedDate) Is Null) AND ((tbl2006Refresh.UserOKed)=Yes) AND ((tbl2006Refresh.Completed)=No));

 

[Remou]

Something like:

[tt]strSQL="Update (tbl2006Full INNER JOIN tbl2006Refresh " _
& "ON tbl2006Refresh.AssetTag = tbl2006Full.AssetTag) " _
& "INNER JOIN tblAUsers ON " _
& "tbl2006Full.NetID = tblAUsers.NetID " _
& "Set EmailSent=True " _
& "WHERE (((tbl2006Refresh.RefreshDate)<(Date()+18)) " _
& "AND ((tbl2006Refresh.TicketOpenedDate) Is Null) " _
& "AND ((tbl2006Refresh.UserOKed)=Yes) " _
& "AND ((tbl2006Refresh.Completed)=No));"
DoCmd.RunSQL strSQL
'Or
'CurrentDB.Execute strSQL[/tt]

I cannot tell if this will work with your set-up, you may need to look at indexes (and my syntax ).

 

[rlmorgan]

Thanks for the help. I'll give it a try on Monday.