|
Home > Archive > MS Access database support > April 2006 > Emailing from Access
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Emailing from Access
|
|
| cvollberg via AccessMonster.com 2006-04-05, 9:35 am |
| Good morning,
I have this procedure that is run that pulls email names that match our
global database and emails reports to automatically to each group of names,
using click yes and Outlook. I have created a trap, error handler that skips
the cost center that has an invalid name it. My question is, if the field
has four names in it and only one of them is invalid, how can I still send to
the three that are good and skip the one that is invalid?
here is the code I am using:
Sub EMail_BranchPipeline
()
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim qdf As QueryDef
Dim BRANCH As String
Dim BranchName As String
Dim BREml As String
Dim stDocName As String
Dim varme As Variant
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("qryPIPWEmailList")
Let stDocName = " rptPIPWDetailPipelin
e"
rst.MoveFirst
DoCmd.SetWarnings False
DoCmd.OpenForm ("frmPIPWReportGen")
Do Until rst.EOF
Let BRANCH = rst!Branch5dgt
Let BranchName = rst![Branch Name]
Let BREml = rst!PipelineEmail
Let [Forms]!& #91;frmPIPWReportGen
]![BrName].Value = BranchName
Let [Forms]!& #91;frmPIPWReportGen
]![BRANCH].Value = BRANCH
varme = DCount("[Loan #]", " qryPIPWDetailPipelin
e")
If (varme <> 0 And [Forms]!& #91;frmBPProdRepDate
Input]![Check22] = False)
Then DoCmd.OpenReport stDocName, acViewNormal
On Error GoTo ErrorHandler
If (varme <> 0 And [Forms]!& #91;frmBPProdRepDate
Input]![Check19] = False)
Then DoCmd.SendObject acSendReport, stDocName, acFormatSNP, BREml, , ,
BranchName & " Pipeline Reports", "Pipeline report for " & BranchName & "
attached. This is a multiple page report, please make sure you view or print
all pages.", False
rst.MoveNext
Loop
rst.Close
Set dbs = Nothing
DoCmd.Close acForm, ("frmPIPWReportGen")
DoCmd.SetWarnings True
ErrorHandler:
If Err.Number = 2295 Then
MsgBox "Error number " & Err.Number & ": " & Err.Description + " For
Cost Center " + [Forms]!& #91;frmCMSHReportGen
]![CstCntr]
Resume Next
End If
End Sub
--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...access/200604/1
| |
| pietlinden@hotmail.com 2006-04-05, 1:44 pm |
| My question is, if the field
has four names in it and only one of them is invalid, how can I still
send to
the three that are good and skip the one that is invalid?
The easiest way would be to normalize, so the e-mails are in separate
records. Otherwise, you have to use SPLIT to break the single field
into some number of e-mail addresses and then check them.
| |
| cvollberg via AccessMonster.com 2006-04-05, 1:44 pm |
| That is easier said than done, I did not build this monster, I am just trying
to fix and yes normalize. The way it is set up, there are 147 records in the
table, all branches, each branch gets emailed reports seperately, sometimes
only one person, some are three and four, there are only 5 columns in the
table, three different email list fields, branch name and number. to give
you an idea how it looks here is a portion of one record, this table is
connected to about 25 queries and reports:
BRANCH EmailLoanDeta
il
6211 Tammy Hawks;Vernesa Dzinic;Charles Richardson;Lauralie Roylance;David
Abrahamson
PostClosingEmail
Debbie Schoonover;Tammy Hawks;Vernesa Dzinic;David Abrahamson
PipelineEmail
Vernesa Dzinic;Tammy Hawks;David Abrahamson;Debbie Renner
pietlinden@hotmail.com wrote:
>My question is, if the field
>has four names in it and only one of them is invalid, how can I still
>send to
>the three that are good and skip the one that is invalid?
>
>The easiest way would be to normalize, so the e-mails are in separate
>records. Otherwise, you have to use SPLIT to break the single field
>into some number of e-mail addresses and then check them.
--
Message posted via http://www.webservertalk.com
| |
| Tim Marshall 2006-04-05, 1:44 pm |
| cvollberg via webservertalk.com wrote:
> That is easier said than done, I did not build this monster, I am just trying
> to fix and yes normalize. The way it is set up, there are 147 records in the
HI, I think I, and many others, have been where you are, before. In my
opinion, it's best to fix the application first. You can explain to
whoever has tasked you with this that so-and-so who created it did not
follow database conventions properly and that the application *must* be
properly fixed or this current email task cannot deliver a reliable
result. Further, any future enhancements, including proper
representation of historical trends, etc, will take an extraordinarily
unnecessarily long time.
If "they" come back with the catch all "but it works, so who cares about
database conventions?" you can respond by saying an unqualified
electrician who knows nothing about building electrical codes can wire a
new house and the lights and appliances will work, but eventually
something will happen and an electrical fire will burn down the house.
This situation is precisely one of the reasons so many non-Access
developers snub MS Access. It is just too easy for some yahoo like the
person who designed the mdb you're working on to put together something
that is claptrap and unacceptable but still get it to work and impress
users and senior management.
The other thing to consider is that your questions are a of no help to
anyone looking at usenet archives in the future and that you are wasting
the time of good-hearted folks like Piet who want to try and help. Your
organization is throwing good money after bad by forcing you to work on
this.
Not a criticism of you, at all, but of the dingbat that put together
your app in the first place. Using the electrical analogy, like any
condemned building, this one needs to be torn down and cleared out *or*
the deficiencies properly corrected before you can be issued with a
permit to do any further work! 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
| |
| cvollberg via AccessMonster.com 2006-04-05, 1:44 pm |
| Tim,
I understand what you mean, I have only been working on this a month and it
is a nightmare. I just trying to get some ideas on this, I am only here for
another week, I am a temp consultant trying to fix and get it to work
properly, there is just not enough time to fix it correctly and I am sorry if
I wasted anyone's time. There is so much dam code, not properly documented,
I spend half my time deciphering, no error handlers and I could go on and on.
but anyway thanks for the advise.
Tim Marshall wrote:
>
>HI, I think I, and many others, have been where you are, before. In my
>opinion, it's best to fix the application first. You can explain to
>whoever has tasked you with this that so-and-so who created it did not
>follow database conventions properly and that the application *must* be
>properly fixed or this current email task cannot deliver a reliable
>result. Further, any future enhancements, including proper
>representation of historical trends, etc, will take an extraordinarily
>unnecessarily long time.
>
>If "they" come back with the catch all "but it works, so who cares about
>database conventions?" you can respond by saying an unqualified
>electrician who knows nothing about building electrical codes can wire a
>new house and the lights and appliances will work, but eventually
>something will happen and an electrical fire will burn down the house.
>
>This situation is precisely one of the reasons so many non-Access
>developers snub MS Access. It is just too easy for some yahoo like the
>person who designed the mdb you're working on to put together something
>that is claptrap and unacceptable but still get it to work and impress
>users and senior management.
>
>The other thing to consider is that your questions are a of no help to
>anyone looking at usenet archives in the future and that you are wasting
>the time of good-hearted folks like Piet who want to try and help. Your
>organization is throwing good money after bad by forcing you to work on
>this.
>
>Not a criticism of you, at all, but of the dingbat that put together
>your app in the first place. Using the electrical analogy, like any
>condemned building, this one needs to be torn down and cleared out *or*
>the deficiencies properly corrected before you can be issued with a
>permit to do any further work! 8)
>
--
Message posted via http://www.webservertalk.com
| |
| Tim Marshall 2006-04-05, 8:28 pm |
| cvollberg via webservertalk.com wrote:
> there is just not enough time to fix it correctly and I am sorry if
> I wasted anyone's time.
I didn't mean to come across harshly, sorry if I did. 8) It was meant
to be aimed at the twittertwits who foisted their garbage on you.
> There is so much dam code, not properly documented,
> I spend half my time deciphering, no error handlers and I could go on and on.
> but anyway thanks for the advise.
Do you do a final report for your time there? You may want to indicate
the app is broken/stupid/silly and needs to be properly re-done - it
might help you get work with them in the future.
All the best.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
| |
| cvollberg via AccessMonster.com 2006-04-05, 8:28 pm |
| I do next Friday and that is when they will get my opinions on this thing
that some nitwit built. there really is a lot of good code in the thing, but
it must be normalized and restructured. And maybe it will help extend my
contract. thanks and take care.
Tim Marshall wrote:
>
>I didn't mean to come across harshly, sorry if I did. 8) It was meant
>to be aimed at the twittertwits who foisted their garbage on you.
>
>
>Do you do a final report for your time there? You may want to indicate
>the app is broken/stupid/silly and needs to be properly re-done - it
>might help you get work with them in the future.
>
>All the best.
>
--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...access/200604/1
| |
|
| I would use the InStr(1, strName, ";") which will tell you if there is
a semi-colon in the field.
If there is, then run code that would seperate out the names and put
them in a seperate table.
Then you can email each one individually and remove those names that
are not valid.
I would use code that would track the start position of the current
name, then find increment the end position until you find a semi-colon.
Then grab the information in between and you have a name.
Next you change the value of the start position so that it is the value
of the last position + 1. Then begin to move the end position until you
either find another semi-colon or you reach the end of the field. Be
aware that I am writing this here in the reply window to the forum and
have not tested this code for sytax errors, but you will get the idea:
Dim intStart As Integer
Dim intEnd As Integer
Dim intLen As Integer
Dim strNames As String
strNames = "Debbie Schoonover;Tammy Hawks;Vernesa Dzinic;David
Abrahamson" (use code to retrieve this from your recordset)
intLen = Len(strNames")
intStart = 1
intEnd = 1
If InStr(1, strNames, ";") > 0 Then
While intEnd <> intLen
intEnd = intEnd + 1
If Mid(strNames, intEnd, 1) = ";" Then
strFoundName = Mid(strNames, intStart, (intLen - intEnd) -
intEnd))
'Write the name to a table
intStart = intEnd + 1
intEnd = intEnd +1
End If
Wend
End If
| |
| pietlinden@hotmail.com 2006-04-05, 8:28 pm |
| oh, if the list of names is always delimited with semi-colons, then you
can use SPLIT and use semi-colon as the delimiter. That will return an
array and you can loop through it using something like
Public Sub ShowNames(ByVal strNames As String)
Dim varNames As Variant
Dim intCounter As Integer
varNames = Split(strNames, ";")
For intCounter = 0 To UBound(varNames)
Debug.Print varNames(intCounter)
'---PROCESS LIST HERE----
Next intCounter
End Sub
then once you get to the '---PROCESS LIST HERE line, you can do
whatever you want with the the individual names - add them to an e-mail
recipients collection or whatever.
(or once the stuff is split out, you can write the individual values to
a table.... do a little cleanup with a few simple bits of code...)
HTH,
Pieter
|
|
|
|
|