I am trying to use the following post results ( http://www.thescripts.com/forum/thread189759.html) and add another requirement. I need to send the results to just the email addresses that the query specifies for each record... Essentially this is a make shift Ordering Tool and I want to be able to notify the receiver of the order and its specifics.
q_Order_Detail_4email consists of
[Contact Email],[ID],[Product_1],[Quantity_1] and [Product_PO_1]
Example: Hello@mail.com,956,Modem,1000,xyz123 Hello2@mail.com,957,USB,500,zyx321
In this case I need for two emails to be generated with one line of data in the results on each email. - Function GetOrderBody() As String
-
On Error GoTo GetOrderBody_Err
-
-
Dim dbs As DAO.Database
-
Dim rst As DAO.Recordset
-
Dim strInfo As String
-
-
Set dbs = CurrentDb
-
Set rst = dbs.OpenRecordset("q_Order_Detail_4email", dbOpenForwardOnly, dbReadOnly)
-
-
While Not rst.EOF
-
-
strInfo = strInfo & rst!ID & vbTab
-
strInfo = strInfo & rst!Product_1 & vbTab
-
strInfo = strInfo & rst!Quantity_1 & vbTab
-
strInfo = strInfo & rst!Product_PO_1 & vbCrLf
-
rst.MoveNext
-
Wend
-
-
GetOrderBody = strInfo
-
-
GetOrderBody_Exit:
-
Exit Function
-
-
GetOrderBody_Err:
-
MsgBox Error$
-
Resume GetOrderBody_Exit
-
-
End Function
- Public Function EOT_OPR_SendEMail()
-
-
Dim db As DAO.Database
-
Dim MailList As DAO.Recordset
-
Dim MyOutlook As Outlook.Application
-
Dim MyMail As Outlook.MailItem
-
Dim Subjectline As String
-
Dim BodyFile As String
-
Dim fso As FileSystemObject
-
Dim MyBody As TextStream
-
Dim MyBodyText As String
-
-
-
Set fso = New FileSystemObject
-
-
' First, we need to know the subject.
-
' We can't very well be sending around blank messages...
-
-
Subjectline$ = "Equipment Ordering Tool - Order Placed"
-
'Subjectline$ = InputBox$("Please enter the subject line for this mailing.", _
-
"We Need A Subject Line!")
-
-
' If there's no subject, call it a day.
-
-
If Subjectline$ = "" Then
-
MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
-
"Quitting...", vbCritical, "E-Mail Merger"
-
Exit Function
-
End If
-
-
' Now we need to put something in our letter...
-
-
BodyFile$ = "\\CO1860-IMRPTNG\Automation\EOT-Order_Placed_Recipient.txt"
-
-
' If there's nothing to say, call it a day.
-
-
If BodyFile$ = "" Then
-
MsgBox "No body, no message." & vbNewLine & vbNewLine & _
-
"Quitting...", vbCritical, "I Ain't Got No-Body!"
-
Exit Function
-
End If
-
-
' Check to make sure the file exists...
-
If fso.FileExists(BodyFile$) = False Then
-
MsgBox "The body file isn't where you say it is. " & vbNewLine & vbNewLine & _
-
"Quitting...", vbCritical, "I Ain't Got No-Body!"
-
Exit Function
-
End If
-
-
' Since we got a file, we can open it up.
-
Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)
-
-
' and read it into a variable.
-
MyBodyText = MyBody.ReadAll
-
-
' and close the file.
-
MyBody.Close
-
-
' Now, we open Outlook for our own device..
-
Set MyOutlook = New Outlook.Application
-
-
-
' Set up the database and query connections
-
-
Set db = CurrentDb()
-
-
Set MailList = db.OpenRecordset("q_Order_Detail_4email", dbOpenForwardOnly, dbReadOnly)
-
-
' now, this is the meat and potatoes.
-
' this is where we loop through our list of addresses,
-
' adding them to e-mails and sending them.
-
-
Do Until MailList.EOF
-
-
' This creates the e-mail
-
-
Set MyMail = MyOutlook.CreateItem(olMailItem)
-
-
' This addresses it
-
MyMail.To = MailList("Contact Email")
-
-
'This gives it a subject
-
MyMail.Subject = Subjectline$
-
-
'This gives it the body
-
MyMail.Body = MyBodyText
-
-
-
'If you want to send an attachment
-
'uncomment the following line
-
-
'MyMail.Attachments.Add "c:\dbgout.txt", olByValue, 1, "My Displayname"
-
'MyMail.Attachments.Add "c:\dbgout.txt", olByValue, 1, "My Displayname2"
-
-
' To briefly describe:
-
' "c:\myfile.txt" = the file you want to attach
-
'
-
' olByVaue = how to pass the file. olByValue attaches it, olByReference creates a shortcut.
-
' the shortcut only works if the file is available locally (via mapped or local drive)
-
'
-
' 1 = the position in the outlook message where to attachment goes. This is ignored by most
-
' other mailers, so you might want to ignore it too. Using 1 puts the attachment
-
' first in line.
-
'
-
' "My Displayname" = If you don't want the attachment's icon string to be "c:\myfile.txt" you
-
' can use this property to change it to something useful, i.e. "4th Qtr Report"
-
-
-
-
'This sends it!
-
MyMail.Send
-
-
'Some people have asked how to see the e-mail
-
'instead of automaticially sending it.
-
'Uncomment the next line
-
'And comment the "MyMail.Send" line above this.
-
-
'MyMail.Display
-
-
-
-
'And on to the next one...
-
MailList.MoveNext
-
-
Loop
-
-
'Cleanup after ourselves
-
-
Set MyMail = Nothing
-
-
-
'Uncomment the next line if you want Outlook to shut down when its done.
-
'Otherwise, it will stay running.
-
-
'MyOutlook.Quit
-
Set MyOutlook = Nothing
-
-
MailList.Close
-
Set MailList = Nothing
-
db.Close
-
Set db = Nothing
-
-
End Function
Any help would be greatly appreciated.
1 6521
I don't think you need to be that complicated. Try this instead.... - Function GetOrderBody() As String
-
On Error GoTo GetOrderBody_Err
-
Dim dbs As DAO.Database
-
Dim rst As DAO.Recordset
-
Dim strInfo As String
-
-
Set dbs = CurrentDb
-
Set rst = dbs.OpenRecordset("q_Order_Detail_4email", dbOpenForwardOnly, dbReadOnly)
-
-
Do Until rst.EOF
-
-
strInfo = strInfo & rst!Id & vbTab
-
strInfo = strInfo & rst!Product_1 & vbTab
-
strInfo = strInfo & rst!Quantity_1 & vbTab
-
strInfo = strInfo & rst!Product_PO_1 & vbCrLf
-
DoCmd.SendObject acSendNoObject, , , rst![Contact Email], , , "Order Details", strInfo
-
rst.MoveNext
-
Loop
-
-
GetOrderBody_Exit:
-
Exit Function
-
-
GetOrderBody_Err:
-
MsgBox Error$
-
Resume GetOrderBody_Exit
-
-
End Function
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Wescotte |
last post by:
Here is a small sample program I wrote in PHP (running off Apache
1.3.31 w/ PHP 5.0.1) to help illustrates problem I'm having. The data
base is using DB2 V5R3M0. The client is WinXP machine using...
|
by: serendipity |
last post by:
Hi,
I'm not sure if this is possible as i've googled everywhere, but i have a
select query that returns a customer record with their associated sales
orders. I would like to automate a process...
|
by: DigitalRick |
last post by:
I have been running CDONTS in my ASPpages to send emails to me sent from my
guestbook. It had been working fine untill I upgraded to Server 2003 (I am
also running Exchange 2003) all locally. I...
|
by: james |
last post by:
I am trying to use Filestream to read a file ( .DAT) that contains values in HEX that I want to convert to text. I know the
different offset addresses for each portion of the data I am trying to...
|
by: Mike Charney |
last post by:
Is there a simple way to send SMTP email from Access VBA?
Mike
m charney at dunlap hospital dot org
|
by: divya |
last post by:
I have a form which has a textarea,name - txtTo where he adds email
addresses.Now when he clicks on sendemail I want to open a mailto link
with addresses taken from textarea.
Example
I added...
|
by: Mike |
last post by:
I have a page with a textbox that a user can enter in mutliple email addresses such as:
user1@yahoo.com;user2@yahoo.com;user3@gmail.com;
and so on, I then have a foreach loop to get all of the...
|
by: kennykenn |
last post by:
Hi,
Ive producd code to send an email after capturing info off a form,it works fine locally but when i put it live it doesnt work! the code is stopin at 'msg.send' any ideas,
here the code!
...
|
by: chris |
last post by:
I need to maintain a list of subscribers to an email list for a
"newsletter" that will be sent via a web form probably once a month.
I anticipate low numbers--tens to maybe one hundred subscribers...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |