Send email message from Access

G

Guest

Hi

My code below is an event procedure when I double click from a field (JPL1 -
"strjpl1") in a form and it will retrieve only data from current data. what i
need is your advise or show me sample code should i have more than 1 data
i.e. "PN" (strpn). anyone there can help me. i have no programming background
but I read alot and gather info from website. however i tried to get this
moving for a while now. so now i try on this website.....hope someone can
assist me

Private Sub JPL1_DblClick(Cancel As Integer)
Dim stWhere As String '-- Criteria for DLookup
Dim varTo As Variant '-- Address for SendObject
Dim varbcc As Variant '-- bcc address for sendobject
Dim stText, stText1, stText2 As String '-- E-mail text
Dim RecDate As Variant '-- Rec date for e-mail text
Dim stSubject As String '-- Subject line of e-mail
Dim stTicketID As String '-- The ticket ID from form
Dim stWho As String '-- Reference to tblUsers
Dim stHelpDesk As String '-- Person who assigned ticket
Dim STCPO As String '-- Customer PO from form
Dim stjpo As String '-- Janco PO from form
Dim stjpl1 As String '-- Janco PO from form
Dim stKEYACCOUNTHOLDER As String '-- Key account holder
Dim stpn, stdesription, stsn As String
Dim sttypeofcert As String
Dim stmatcertno As String
Dim stawb As String
Dim stshipqty1 As Integer
Dim stfltno1 As String
Dim stshipfltdate1 As String
Dim strSQL As String '-- Create SQL update statement
Dim errLoop As Error

'-- Combo of names to assign ticket to
stWho = Format(Me.BUYER)
varTo = Format(Me.emailadd)
varcc = IIf(Format(Me.SG_SALES) = "AJAY", "(e-mail address removed)",
IIf(Format(Me.SG_SALES) = "ALVIN", "(e-mail address removed)",
IIf(Format(Me.SG_SALES) = "JOE", "(e-mail address removed)",
IIf(Format(Me.SG_SALES) = "ERIC", "(e-mail address removed)", ""))))
varbcc = "admin" & ";" & "management"
STCPO = Format(Me.C_PO_NO)
stjpo = Format(Me.J_PO_NO)
stjpl1 = Format(Me.JPL1)
stKEYACCOUNTHOLDER = Format(Me.KEY_ACCOUNT_HOLDER)
stpn = Format(Me.PN)
stdescription = Format(Me.DESCRIPTION)
stsn = Format(Me.SN)
sttypeofcert = Format(Me.[TYPE OF CERTS])
stmatcertno = Format(Me.MATERIAL_CERT_NO)
stawb = Format(Me.AWB1)
stshipqty1 = Format(Me.SHIP_QTY1)
stfltno1 = Format(Me.FLT_NO1)
stshipfltdate1 = Format(Me.SHIP_FLT_DATE1)


stSubject = "Shipping Advice for your PO Ref: " & STCPO & " / Our Ref:
" & stjpo & " for Part # " & stpn
stText = "Dear " & stWho & "," & Chr$(13) & Chr$(13) & Chr$(13) & _
"This is to advise shipping details for following Purchase
Order; " & Chr$(13) & Chr$(13) & _
"Your PO #: " & STCPO & Chr$(13) & _
"Our Ref. : " & stjpo & Chr$(13) & _
"Part # : " & stpn & " Qty " & stshipqty1 & Chr$(13) & _
"Serial # : " & stsn & Chr$(13) & _
"Mat. Cert: " & sttypeofcert & "." & stmatcertno & Chr$(13) &
Chr$(13) & _
"Our PL # : " & stjpl1 & Chr$(13) & _
"HAWB/MAWB: " & stawb & Chr$(13) & _
"Flight # : " & stfltno1 & Chr$(13) & _
"Date Ship: " & Format([stshipfltdate1], "medium date") &
Chr$(13) & Chr$(13) & Chr$(13) & _
"Best regards " & Chr$(13) & _
stKEYACCOUNTHOLDER & Chr$(13) & Chr$(13) & _
"JANCO Aviation Pte Ltd" & Chr$(13) & _
"10 Anson Road" & Chr$(13) & _
"#24-07 International Plaza" & Chr$(13) & _
"Singapore 079903" & Chr$(13) & _
"Tel: +65 63243248" & IIf(stKEYACCOUNTHOLDER = "norhaya", " x
229", IIf(stKEYACCOUNTHOLDER = "Tammy", " x 213", IIf(stKEYACCOUNTHOLDER =
"mark", " x 230", IIf(stKEYACCOUNTHOLDER = "sally", " x 210",
IIf(stKEYACCOUNTHOLDER = "jennifer", " x 211", IIf(stKEYACCOUNTHOLDER = "ting
bee", " x 228", "")))))) & Chr$(13) & _
"Fax: +65 63243249" & Chr$(13) & _
"email: " & IIf(stKEYACCOUNTHOLDER = "norhaya",
"norhaya.saibi", IIf(stKEYACCOUNTHOLDER = "tammy", "tammy.tan",
IIf(stKEYACCOUNTHOLDER = "mark", "mark.oh", IIf(stKEYACCOUNTHOLDER = "sally",
"sally.chia", IIf(stKEYACCOUNTHOLDER = "jennifer", "jenniferliew",
IIf(stKEYACCOUNTHOLDER = "ting bee", "kwa.tingbee", "")))))) &
"@jancogroup.com" & Chr$(13) & Chr$(13) & _
"For sales enquiries or critical requirement after office
hours/on weekends/public" & Chr$(13) & _
"holidays, please contact us at mobile tel # +65 9796-6435;
email: (e-mail address removed)" & Chr$(13) & _
"This is an automated message. Please do not respond to this
e-mail."

'Write the e-mail content for sending to assignee
DoCmd.SendObject , , acFormatTXT, varTo, varcc, varbcc, stSubject,
stText, -1

End Sub
 
G

Guest

hi,
have a look at this website:
http://www.granite.ab.ca/access/email.htm
You will find sample code which illustrates how to loop through a recordset
and send e-mails.
This should get you started.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)


norhaya said:
Hi

My code below is an event procedure when I double click from a field (JPL1 -
"strjpl1") in a form and it will retrieve only data from current data. what i
need is your advise or show me sample code should i have more than 1 data
i.e. "PN" (strpn). anyone there can help me. i have no programming background
but I read alot and gather info from website. however i tried to get this
moving for a while now. so now i try on this website.....hope someone can
assist me

Private Sub JPL1_DblClick(Cancel As Integer)
Dim stWhere As String '-- Criteria for DLookup
Dim varTo As Variant '-- Address for SendObject
Dim varbcc As Variant '-- bcc address for sendobject
Dim stText, stText1, stText2 As String '-- E-mail text
Dim RecDate As Variant '-- Rec date for e-mail text
Dim stSubject As String '-- Subject line of e-mail
Dim stTicketID As String '-- The ticket ID from form
Dim stWho As String '-- Reference to tblUsers
Dim stHelpDesk As String '-- Person who assigned ticket
Dim STCPO As String '-- Customer PO from form
Dim stjpo As String '-- Janco PO from form
Dim stjpl1 As String '-- Janco PO from form
Dim stKEYACCOUNTHOLDER As String '-- Key account holder
Dim stpn, stdesription, stsn As String
Dim sttypeofcert As String
Dim stmatcertno As String
Dim stawb As String
Dim stshipqty1 As Integer
Dim stfltno1 As String
Dim stshipfltdate1 As String
Dim strSQL As String '-- Create SQL update statement
Dim errLoop As Error

'-- Combo of names to assign ticket to
stWho = Format(Me.BUYER)
varTo = Format(Me.emailadd)
varcc = IIf(Format(Me.SG_SALES) = "AJAY", "(e-mail address removed)",
IIf(Format(Me.SG_SALES) = "ALVIN", "(e-mail address removed)",
IIf(Format(Me.SG_SALES) = "JOE", "(e-mail address removed)",
IIf(Format(Me.SG_SALES) = "ERIC", "(e-mail address removed)", ""))))
varbcc = "admin" & ";" & "management"
STCPO = Format(Me.C_PO_NO)
stjpo = Format(Me.J_PO_NO)
stjpl1 = Format(Me.JPL1)
stKEYACCOUNTHOLDER = Format(Me.KEY_ACCOUNT_HOLDER)
stpn = Format(Me.PN)
stdescription = Format(Me.DESCRIPTION)
stsn = Format(Me.SN)
sttypeofcert = Format(Me.[TYPE OF CERTS])
stmatcertno = Format(Me.MATERIAL_CERT_NO)
stawb = Format(Me.AWB1)
stshipqty1 = Format(Me.SHIP_QTY1)
stfltno1 = Format(Me.FLT_NO1)
stshipfltdate1 = Format(Me.SHIP_FLT_DATE1)


stSubject = "Shipping Advice for your PO Ref: " & STCPO & " / Our Ref:
" & stjpo & " for Part # " & stpn
stText = "Dear " & stWho & "," & Chr$(13) & Chr$(13) & Chr$(13) & _
"This is to advise shipping details for following Purchase
Order; " & Chr$(13) & Chr$(13) & _
"Your PO #: " & STCPO & Chr$(13) & _
"Our Ref. : " & stjpo & Chr$(13) & _
"Part # : " & stpn & " Qty " & stshipqty1 & Chr$(13) & _
"Serial # : " & stsn & Chr$(13) & _
"Mat. Cert: " & sttypeofcert & "." & stmatcertno & Chr$(13) &
Chr$(13) & _
"Our PL # : " & stjpl1 & Chr$(13) & _
"HAWB/MAWB: " & stawb & Chr$(13) & _
"Flight # : " & stfltno1 & Chr$(13) & _
"Date Ship: " & Format([stshipfltdate1], "medium date") &
Chr$(13) & Chr$(13) & Chr$(13) & _
"Best regards " & Chr$(13) & _
stKEYACCOUNTHOLDER & Chr$(13) & Chr$(13) & _
"JANCO Aviation Pte Ltd" & Chr$(13) & _
"10 Anson Road" & Chr$(13) & _
"#24-07 International Plaza" & Chr$(13) & _
"Singapore 079903" & Chr$(13) & _
"Tel: +65 63243248" & IIf(stKEYACCOUNTHOLDER = "norhaya", " x
229", IIf(stKEYACCOUNTHOLDER = "Tammy", " x 213", IIf(stKEYACCOUNTHOLDER =
"mark", " x 230", IIf(stKEYACCOUNTHOLDER = "sally", " x 210",
IIf(stKEYACCOUNTHOLDER = "jennifer", " x 211", IIf(stKEYACCOUNTHOLDER = "ting
bee", " x 228", "")))))) & Chr$(13) & _
"Fax: +65 63243249" & Chr$(13) & _
"email: " & IIf(stKEYACCOUNTHOLDER = "norhaya",
"norhaya.saibi", IIf(stKEYACCOUNTHOLDER = "tammy", "tammy.tan",
IIf(stKEYACCOUNTHOLDER = "mark", "mark.oh", IIf(stKEYACCOUNTHOLDER = "sally",
"sally.chia", IIf(stKEYACCOUNTHOLDER = "jennifer", "jenniferliew",
IIf(stKEYACCOUNTHOLDER = "ting bee", "kwa.tingbee", "")))))) &
"@jancogroup.com" & Chr$(13) & Chr$(13) & _
"For sales enquiries or critical requirement after office
hours/on weekends/public" & Chr$(13) & _
"holidays, please contact us at mobile tel # +65 9796-6435;
email: (e-mail address removed)" & Chr$(13) & _
"This is an automated message. Please do not respond to this
e-mail."

'Write the e-mail content for sending to assignee
DoCmd.SendObject , , acFormatTXT, varTo, varcc, varbcc, stSubject,
stText, -1

End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

retrieve multiple data for one criteria - urgent help. 1
Adding Attachment to Code to send an Email Message 1
Send Email with Access 2007 1
Unable to send email in HTML format 1
True or False for SNP or PDF format to email 12
Can access automatically insert "from" field when sending email 3
Sending Report via Email 3
Adding a CC to email code! 7

Top