Emailing from Access

[cvollberg via AccessMonster.com]

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_BranchPip eline()

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.OpenRecords et("qryPIPWEmai lList")
Let stDocName = "rptPIPWDetailP ipeline"
rst.MoveFirst
DoCmd.SetWarnin gs False
DoCmd.OpenForm ("frmPIPWReport Gen")
Do Until rst.EOF
Let BRANCH = rst!Branch5dgt
Let BranchName = rst![Branch Name]
Let BREml = rst!PipelineEma il
Let [Forms]![frmPIPWReportGe n]![BrName].Value = BranchName
Let [Forms]![frmPIPWReportGe n]![BRANCH].Value = BRANCH
varme = DCount("[Loan #]", "qryPIPWDetailP ipeline")
If (varme <> 0 And [Forms]![frmBPProdRepDat eInput]![Check22] = False)
Then DoCmd.OpenRepor t stDocName, acViewNormal

On Error GoTo ErrorHandler
If (varme <> 0 And [Forms]![frmBPProdRepDat eInput]![Check19] = False)
Then DoCmd.SendObjec t 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, ("frmPIPWReport Gen")
DoCmd.SetWarnin gs True

ErrorHandler:
If Err.Number = 2295 Then
MsgBox "Error number " & Err.Number & ": " & Err.Description + " For
Cost Center " + [Forms]![frmCMSHReportGe n]![CstCntr]

Resume Next
End If

End Sub

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200604/1

[pietlinden@hotmail.com]

Re: Emailing from Access

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]

Re: Emailing from Access

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 EmailLoanDetail
6211 Tammy Hawks;Vernesa Dzinic;Charles Richardson;Laur alie Roylance;David
Abrahamson
PostClosingEmai l
Debbie Schoonover;Tamm y Hawks;Vernesa Dzinic;David Abrahamson
PipelineEmail
Vernesa Dzinic;Tammy Hawks;David Abrahamson;Debb ie Renner

pietlinden@hotm ail.com wrote:[color=blue]
>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.[/color]

--
Message posted via http://www.accessmonster.com

[Tim Marshall]

Re: Emailing from Access

cvollberg via AccessMonster.c om wrote:
[color=blue]
> 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[/color]

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]

Re: Emailing from Access

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:[color=blue][color=green]
>> 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[/color]
>
>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
>representati on of historical trends, etc, will take an extraordinarily
>unnecessaril y 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)
>[/color]

--
Message posted via http://www.accessmonster.com

[Tim Marshall]

Re: Emailing from Access

cvollberg via AccessMonster.c om wrote:[color=blue]
> there is just not enough time to fix it correctly and I am sorry if
> I wasted anyone's time.[/color]

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.
[color=blue]
> 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.[/color]

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]

Re: Emailing from Access

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:[color=blue][color=green]
> > there is just not enough time to fix it correctly and I am sorry if
>> I wasted anyone's time.[/color]
>
>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.
>[color=green]
>> 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.[/color]
>
>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.
>[/color]

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200604/1

[Ted]

Re: Emailing from Access

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;Tamm y 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]

Re: Emailing from Access

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(intCou nter)
'---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