Katrina-
For starters, your Append query should look like:
INSERT INTO [SEND TBL] ( [CONTRACT START DATE], [CONTRACT END DATE], [VENDOR
E-MAIL], [CONTRACT ID], [E-MAIL], [CONTACT PERSON], [VENDOR ID], [CONTRACT
TITLE], [LEGAL EMAIL] )
SELECT [Expired Contracts].[CONTRACT START DATE], [Expired Contracts].[CONTRACT
END DATE], [Expired Contracts].[VENDOR E-MAIL], [Expired Contracts].[CONTRACT
ID], [Expired Contracts].[E-MAIL], [Expired Contracts].[CONTACT PERSON],
[Expired Contracts].[VENDOR ID], [Expired Contracts].[CONTRACT TITLE], [Expired
Contracts].[LEGAL EMAIL], *
FROM [Expired Contracts];
I guess I didn't make it clear that all your send email code should be in SEND
FRM. Move all this code there:
-------------------------------
' Open a recordset on the expiring contracts
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM [SEND TBL] WHERE [SEND EMAIL]
= True")
' Loop through them all
Do Until rst.EOF
' Make sure we have a valid email
If Not IsNull(rst![VENDOR E-MAIL]) Then
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add(rst![VENDOR E-MAIL])
objOutlookRecip.Type = olTo
' Add the CC recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add(rst![E-MAIL])
objOutlookRecip.Type = olCC
' Add the BCC recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add(rst![LEGAL EMAIL])
objOutlookRecip.Type = olBCC
' Set the Subject, Body, and Importance of the message.
.Subject = "Expire Date Approaching"
.Body = "ATTN:" & " " & rst![CONTACT PERSON] & " " & _
"-" & vbCrLf & vbCrLf & "Please be advised that your contract
with the Corpus Christi International Airport (CCIA) will expire on " & _
rst![CONTRACT END DATE] & _
"." & vbCrLf & vbCrLf & "Please contact Irene
Segovia, Sr. Staff Assistant, at (361) 289-0171, ext. 1210 or by email at
irenes@cctexas.com regarding this matter. Your cooperation is greatly
appreciated." & vbCrLf & vbCrLf & "Thank you." & vbCrLf & vbCrLf & "CCIA
Administration"
.Importance = olImportanceHigh 'High importance
' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next
' Should we display the message before sending?
If DisplayMsg Then
.Display
Else
.Save
.Send
End If
End With
Set objOutlook = Nothing
End If
' Get the next one
rst.MoveNext
Loop
' Close out
rst.Close
Set rst = Nothing
Set db = Nothing
--------------------------------------------------
Note that the code is opening the SEND TBL, not your query.
Your SEND FRM should be a Continuous Form with a command button in the header to
run the above code. Emails will be sent only to the rows selected by the user.
Contracts Expiring should have this code:
Private Sub Command2_Click()
Dim db As DAO.Database, rst As DAO.Recordset
If IsNull(DLookup("[CONTRACT END DATE]", "[Expired Contracts]")) Then
MsgBox "There are no contracts expiring."
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "Main Menu"
Else
' Point to this database
Set db = CurrentDb
' Clean out the working table
CurrentDb.Execute "DELETE * FROM [SEND TBL]", dbFailOnError
' Avoid prompts
DoCmd.SetWarnings False
' Load in the rows to select
DoCmd.OpenQuery "Expired Contracts Query"
' Turn on prompts again
DoCmd.SetWarnings True
' Clear the database object
Set db = Nothing
' Open the selection form
DoCmd.OpenForm "SEND FRM"
' Close me
DoCmd.Close acForm, Me.Name
End If
End Sub
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Waco, TX)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of ka0t1c_ang3l
Sent: Wednesday, February 22, 2012 12:46 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: coding for query
John -
I've created the "SEND Tbl" as my temp table. I've also created an append query
based on the expired contracts query. I've also created the "SEND FRM". On the
"Contract Expiring" form I input the number of days and click run, and it gives
me an error in the FROM part of the code. I've also included the delete code in
that coding; however, because I'm getting that error it's unable to open my
second form. I'm sure I have just completed messed up! I'm just not confident
that I did what you asked me to. Please take a look at it. I've uploaded the
databse to the AssistanceNeeded folder under Contract Management System2.
Thanks for all your help.
Katrina
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Katrina-
>
> At the bottom of the code behind your Run button. Last command should be to
> open the second form and close the current one.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Waco, TX)
>
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of ka0t1c_ang3l
> Sent: Wednesday, February 22, 2012 9:56 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: coding for query
>
> John,
>
> I apologize I am really clueless when it comes to this stuff. Where do I put
> the code to delete?
>
> Thanks!
>
> Katrina
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Katrina-
> >
> > To delete:
> >
> > CurrentDb.Execute "DELETE * FROM MyTable", dbFailOnError
> >
> > .. where MyTable is the name of your temp table.
> >
> > To append:
> >
> > Create a new query using Expired Contacts as the source. Change to an
Append
> > query and use the name of your temp table as the target. Drag and drop all
> the
> > fields, then save the query.
> >
> > Use RunQuery to execute it from code - that should pick up the parameter
from
> > your form.
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Waco, TX)
> >
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of ka0t1c_ang3l
> > Sent: Wednesday, February 22, 2012 9:29 AM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Re: coding for query
> >
> > John -
> >
> > I've created a "temp" table and entered all the columns from the query and
> have
> > incuded a yes/no field. However, you lost me when you said run a Delete
query
> > to clear the table, append the records from
> > > your "results" query into that table, then open a form to display the
table.
> > I have no idea how to do this. I'll create a form based on the "temp" table
> as
> > my second form.
> >
> > Thanks!
> >
> > Katrina
> >
> > > contents.
> > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
wrote:
> > >
> > > Katrina-
> > >
> > > You would need a "temp" table that contains all the columns from your
query
> > and
> > > a Yes/No field. Run a Delete query to clear the table, append the records
> > from
> > > your "results" query into that table, then open a form to display the
table
> > > contents. User could click the Yes/No field to "exclude" certain rows,
then
> > > click a button to send an email to those not excluded. Your code to send
> > emails
> > > would be in that second form.
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > SQL Queries for Mere Mortals
> > > http://www.viescas.com/
> > > (Waco, TX)
> > >
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of ka0t1c_ang3l
> > > Sent: Wednesday, February 22, 2012 8:33 AM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: Re: [MS_AccessPros] coding for query
> > >
> > >
> > > John -
> > >
> > > Thanks! I filled in the Legal ID on my Contract Information table and all
> of
> > > the records came up. I have another question. A little back ground
first.
> > On
> > > my main menu I have a "Contracts Expiring" button which opens the
"Contracts
> > > Expiring" form. This is the form you fixed the code on. What I do is
enter
> > the
> > > number of days and hit run and the system will automatically send each
> vendor
> > > with a contract expiring an email advising them of such. However, in my
> > system
> > > I also have short term agreements which were for maybe only a few month,
and
> I
> > > don't want emails going to those vendors since we would not be renewing a
> > > contract with them. Is it possible to select which vendors I want to send
> > > emails to? For example, once I get to the "Contracts Expiring" form and
put
> > in
> > > the number of days when I hit run instead of instantally having the system
> > send
> > > an email to all vendors with expiring contracts it will prompt me with a
> list
> > > that includes a checkbox and those vendors with a checked box will be the
> only
> > > ones who will receive an email. If this is possible what would I have to
do
> > to
> > > get this done?
> > >
> > > Thanks for your help!
> > >
> > > Katrina
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
> wrote:
> > > >
> > > > Katrina-
> > > >
> > > > The query runs fine - and returns no rows because there is nothing in
the
> > > Legal
> > > > ID field in your sample table. The INNER JOINs will return only rows
> where
> > > > there are matching values for all the Joins.
> > > >
> > > > But your code is wrong because you failed to include parens for the
> > additional
> > > > join, and you had an extra ON keyword in the wrong place:
> > > >
> > > > Set rst = db.OpenRecordset("SELECT [VENDOR E-MAIL], [CONTRACT
END
> > > DATE],
> > > > [E-MAIL], [CONTACT PERSON], [LEGAL EMAIL] " & _
> > > > "FROM (([CONTRACT INFORMATION] INNER
> JOIN
> > > > [VENDOR INFORMATION] ON " & _
> > > > "[CONTRACT INFORMATION].[VENDOR ID] =
> > > [VENDOR
> > > > INFORMATION].[VENDOR ID]) " & _
> > > > "INNER JOIN [EMPLOYEE INPUT
> INFORMATION]
> > ON
> > > "
> > > > & _
> > > > "[CONTRACT INFORMATION].[EMPLOYEE ID]
=
> > > > [EMPLOYEE INPUT INFORMATION].[ID])) " & _
> > > > "INNER JOIN [Legal tbl] ON " & _
> > > > "[CONTRACT INFORMATION].[LEGAL ID] =
> > [Legal
> > > > tbl].[LEGAL ID] " & _
> > > > "WHERE [CONTRACT END DATE] < #" &
(Date
> +
> > > > Me.txtNotify) & "#")
> > > >
> > > >
> > > > John Viescas, author
> > > > Microsoft Office Access 2010 Inside Out
> > > > Microsoft Office Access 2007 Inside Out
> > > > Building Microsoft Access Applications
> > > > Microsoft Office Access 2003 Inside Out
> > > > SQL Queries for Mere Mortals
> > > > http://www.viescas.com/
> > > > (Waco, TX)
> > > >
> > > >
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: MS_Access_Professionals@yahoogroups.com
> > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
ka0t1c_ang3l
> > > > Sent: Tuesday, February 21, 2012 4:44 PM
> > > > To: MS_Access_Professionals@yahoogroups.com
> > > > Subject: Re: [MS_AccessPros] coding for query
> > > >
> > > > John -
> > > >
> > > > If i take out the code regarding the legal id and legal email the query
> > works
> > > > fine, so I know it's something to do with the legal tbl i've added.
> > However,
> > > I
> > > > can't figure out what I'm missing or what I've entered incorrectly.
> > > >
> > > > Thanks for your help!
> > > >
> > > > Katrina
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, ka0t1c_ang3l <no_reply@>
> > > > wrote:
> > > > >
> > > > >
> > > > > John -
> > > > >
> > > > > I apologize for the late reply. I've uploaded the database into the
> > > > 2_AssistanceNeeded folder under contract management system2.zip. The
> > Expired
> > > > Contracts query is in there. It was working fine before, but I had to
go
> in
> > > and
> > > > the legal email. The reason for adding in the email is because I want a
> > > > courtsey copy of the email that will be sent to vendors to also go to
our
> > > > attorney.
> > > > >
> > > > > Also, could you please take a look at my contracts expiring form.
The
> > > "Run"
> > > > button event procedure to make sure I added in the legal information
> > > correctly.
> > > > I'm still new to this and am learning as I go. I just want to be sure
> I've
> > > > included everything correctly.
> > > > >
> > > > > Thanks!
> > > > >
> > > > > Katrina
> > > > >
> > > > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
> > > wrote:
> > > > > >
> > > > > > Katrina-
> > > > > >
> > > > > > FROM ([VENDOR INFORMATION] INNER JOIN ([EMPLOYEE INPUT INFORMATION]
> > > > > > INNER JOIN [CONTRACT INFORMATION]
> > > > > > INNER JOIN [Legal tbl]
> > > > > > ON [EMPLOYEE INPUT INFORMATION].ID = [CONTRACT
INFORMATION].[EMPLOYEE
> > ID])
> > >
> > > > > > ON [VENDOR INFORMATION].[VENDOR ID] = [CONTRACT INFORMATION].[VENDOR
> > ID])
> > > > > > ON [CONTRACT INFORMATION].[LEGAL ID] = [Legal tbl].[LEGAL ID]
> > > > > >
> > > > > > I don't see anything wrong with that unless you've misspelled one of
> the
> > > > table
> > > > > > or ID names. Or perhaps there's a problem with an ID data type
> > somewhere.
> > > > > >
> > > > > > What does this look like in Design view?
> > > > > >
> > > > > >
> > > > > > John Viescas, author
> > > > > > Microsoft Office Access 2010 Inside Out
> > > > > > Microsoft Office Access 2007 Inside Out
> > > > > > Building Microsoft Access Applications
> > > > > > Microsoft Office Access 2003 Inside Out
> > > > > > SQL Queries for Mere Mortals
> > > > > > http://www.viescas.com/
> > > > > > (Waco, TX)
> > > > > >
> > > > > >
> > > > > >
> > > > > > -----Original Message-----
> > > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> > ka0t1c_ang3l
> > > > > > Sent: Tuesday, February 21, 2012 8:58 AM
> > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > > Subject: [MS_AccessPros] coding for query
> > > > > >
> > > > > > I have an expired contract query which I use to select vendors who I
> > want
> > > > > > emailed advising that their lease is due to expire. This query
works
> > > fine;
> > > > > > however, I want to add our attorney's email to the query so that she
> can
> > > be
> > > > > > included in the courtesy copy of the email. When I try to add the
> legal
> > > > email
> > > > > > it keeps giving me a syntax error and it higlights the inner join
part
> > of
> > > > the
> > > > > > SQL code. This is what I have.
> > > > > >
> > > > > > ------------
> > > > > > SELECT [CONTRACT INFORMATION].[CONTRACT START DATE], [CONTRACT
> > > > > > INFORMATION].[CONTRACT END DATE], [VENDOR INFORMATION].[VENDOR
> E-MAIL],
> > > > > > [CONTRACT INFORMATION].[CONTRACT ID], [EMPLOYEE INPUT
> > > INFORMATION].[E-MAIL],
> > > > > > [VENDOR INFORMATION].[CONTACT PERSON], [CONTRACT
INFORMATION].[VENDOR
> > ID],
> > > > > > [CONTRACT INFORMATION].[CONTRACT TITLE], [CONTRACT
INFORMATION].[LEGAL
> > > ID],
> > > > > > [Legal tbl].[LEGAL EMAIL]
> > > > > > FROM ([VENDOR INFORMATION] INNER JOIN ([EMPLOYEE INPUT INFORMATION]
> > INNER
> > > > JOIN
> > > > > > [CONTRACT INFORMATION] INNER JOIN [Legal tbl] ON [EMPLOYEE INPUT
> > > > INFORMATION].ID
> > > > > > = [CONTRACT INFORMATION].[EMPLOYEE ID]) ON [VENDOR
> INFORMATION].[VENDOR
> > > ID]
> > > > =
> > > > > > [CONTRACT INFORMATION].[VENDOR ID]) ON [CONTRACT INFORMATION].[LEGAL
> ID]
> > =
> > > > > > [Legal tbl].[LEGAL ID]
> > > > > > WHERE ((([CONTRACT INFORMATION].[CONTRACT END
> > > > DATE])<(Date()+[Forms]![Contracts
> > > > > > Expiring]![txtNotify])));
> > > > > > -----------------
> > > > > >
> > > > > > If anyone can please help me and let me know what it is I'm doing
> wrong
> > > I'd
> > > > > > really appreciate it.
> > > > > >
> > > > > > Thanks!
> > > > > >
> > > > > > Katrina
> > > > > >
> > > > > >
> > > > > >
> > > > > > ------------------------------------
> > > > > >
> > > > > > Yahoo! Groups Links
> > > > > >
> > > > >
> > > >
> > > >
> > > >
> > > >
> > > > ------------------------------------
> > > >
> > > > Yahoo! Groups Links
> > > >
> > >
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar