pass multiple waybill#'s from table to e-mail to trace shipment

  • Thread starter Thread starter M.M.
  • Start date Start date
M

M.M.

Good afternoon

I would like to pass multiple PIN's (waybill#'s) out to trace (via e-mail)
based on a relationship of the lead PIN (where Shipment/Piece = "S") to
Reference I'd like the code to work from an access form into which the PIN
Reference is entered. Sample of table contents below.

I am working in Reports.mdb and PSS_Details is a linked table

The tracing e-mail needs to have as the each PIN on a single line. If
possible I would like the code to create the e-mail with to and subject
details and have it ready to send. Even if I can just get the code to return
this information in text, I can copy and past it into an e-mail.
PSS_Detals Shipment/Piece PIN Reference Pieces Shipment Code Customer Code
S YYZ80334243112 114588 7 MANUAL RZ90031
P YYZ80334243120 114588 7 MANUAL RZ90031
P YYZ80334243138 114588 7 MANUAL RZ90031
P YYZ80334243146 114588 7 MANUAL RZ90031
P YYZ80334243153 114588 7 MANUAL RZ90031
P YYZ80334243161 114588 7 MANUAL RZ90031
P YYZ80334243179 114588 7 MANUAL RZ90031



Thanks for your consideration,

pat
 
Pat,

If you get two postings from me, it's because my link crashed just as I was
posting my first reply. Assuming reply 1 disappeared into the ehter, here we
go again!!

I love working with Outlook (not!!) and have picked up some useful tips in
the short time I've been using these forums (1 week!!), but let's start with
the basic issue of building the data 'block'.

I don't know how familiar you are with VBA coding using recordsets with
either DAO or ADO, so for the moment I'll assume you know some basics and if
not - let me know and I'll fill in the blanks!! My example uses DAO.

OK. you'll need a query ("queryname" in the coding example) based on your
table which does the following:
1 - returns the fields needed to build the data on the email (in the example
code, 'Field1', 'Field2', ..(etc) ... 'FieldN'
2 - has the necessary criteria built in so it only returens the records you
want (shipment/piece = "S")
3 - sorts the records in the order you want them to appear.

The logic is that the process runs through each record in the query, builds
the text string for that record, adds a 'carriage return/linefeed', and moves
to the next record. Finally it 'posts' the whole block back to a form. It
puts a single character space between each element of the data record.


<<coding>>
Dim db as database, rs as recordset
Dim MyText as String

Set db = CurrentDb()
Set rs = db.OpenRecordset("queryname")
MyText = ""

rs.movefirst
do until rs.EOF
mytext = mytext & rs("Field1") & " " & rs("Field2") & " " & ...
rs("FieldN") & vbCrLf
rs.MoveNext
Loop

Forms!MyForm!MyTextBox = MyText

<<end of coding>>

In case you've not seen it before, the variable vbCrLf is the carriage
return/linefeed.

Let me know how you get on with this - you'll end up with a block of text
that you can copy & paste into a blank Email. Once that's sorted I'll happily
take you into automating the whole thing via Outlook. but one thing at a
time...!!!

All the best
 
I do network support and am moderately knowledgeable with access and sql
views, queries, forms, reports etc. I'm not real familiar with VBA but I
have had some success with copy paste and editing newsgroup snips. I'll give
it a rip and let you know

=8^O
 
Pat,

Cheers & good luck - it's only by moving from the familiar to the unfamiliar
that we develop! (as I've found many times!!)

Keep in touch & let me know how it goes and when you're ready to move to the
next stage!!

All the best
 
Back
Top