John -
You are AWESOME! I've tested and the emails are sending; however, I did notice 1 thing. When I select more than 2 vendors that should receive an email only the first 2 emails are being sent out the others are not. What do I need to do to get it to send all the emails out for those I've selected?
Thanks!
Katrina
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> 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@... 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