Generate Email from Access with info from table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working on a Trouble Ticket databse solution for my company. I would
like the form I use to enter new ticket info to generate an email to the
submitting user (address avaible via a seperate table) when they complete the
ticket to include 5 fields from the main table including but not limited to
the ticket number (auto number), the submitting user, the division code, and
its classification. Can someone please help
 
Check that you have a refernece set to the Microsoft Office Outlook 11.0
Object Library.

As is, the code below will create a new MailItem with two files attached
and display it for editing. To send the mail automatically, use the
..Send method of the newMail object. If you're not aware, you can also
create TaskRequestItems as well. If I were building a trouble ticket DB
from scratch I would be inclinded to send out TaskRequests to the the
tech assigned to the TT to have it show up in his/her Outlook.

Dim objOutlook As Outlook.Application
Dim nms As Outlook.NameSpace
Dim newMail As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.application")
Set nms = objOutlook.GetNamespace("MAPI")
Set newMail = objOutlook.CreateItem(olMailItem)

newMail.Body = strMsgText
newMail.To = strEmailAddress
Set newMailAttachments = newMail.Attachments
file1 = strTargetFolder & "\rptInvoiceV1.snp"
file2 = strTargetFolder & "\rptInvoiceRemittanceAdvice.snp"
newMailAttachments.Add file1, olByValue, 1, "Invoice"
newMailAttachments.Add file2, olByValue, 1, "Remittance Advice"
newMail.Subject = "INVOICE: " & strDescription & " (" & Now() & ")"
newMail.Display
-or- newMail.Send 'Send via code

Set newMailAttachments = Nothing
Set newMail = Nothing
Set nms = Nothing
Set objOutlook = Nothing
 
Thank you! It took me a few minutes to research how to add in the reference
libray. The only programming I have done is C. It looks alot like object
orientented programming, therefore it should be pretty easy to manipulate. I
like the idea of assigning the task too. I will have to toy with that once I
get the outlook object down. Thanks again.
 
In the VBA editor go TOOLS>REFERENCES. The references are sorted in a
SELECTED/UNSELECTED order and then alphabetical.
 
I would say it is very much like object oriented programming with each
object having its own methods & properties. I would HIGHLY recommend
snooping around the various object models(VBA Help) in Access & Outlook
to get a feel of what both can do (and then later PowerPoint, Word,
Excel). Also, its not just what you can make each app do, but what you
can do in another app from one - as in using Access to
create/modify/delete Outlook items including snooping around searching
for a particular appointment or mailItem. I just finished code that
allows Access to reach out to Outlook and find appointments that were
created via Access to being with. Way Cool! (and I'm not too much of a geek)
 
I have a new question. I have designed this databse to have one Maintable
(t_TroubleTicket) and another tabke (t_casenotes). When I edit the trouble
ticket main table and want to add a case note (kept in seperate table) I
would like the ticket# to pass from the Maintable to the Case Note table.
How can this be done. I am looking at using the following code on the "Add
Case Note" button.

Dim strticket As String
strticket = Me.tt_Ticket_
'sets value of strticket to be the current ticket number being displayed

DoCmd.OpenForm "f_addcasenote", acNormal, , , , , strticket
' passes the varible strticket to the new form

I would use this pass to set the field cn_ticket#
 
Hel with auto generating e-mail when ACCESS Table is updated.

Need help I would like to have a e-mail auto gernated and sent to user specified in that database. so if a table is written to the database automaticly genrates an email and sends it to the assigned email address. I would also like to be able to have the data copy itself to the body of the e-mail and have the unique key be sent as the title. How can this happen? What do I need to do.
 
Back
Top