SendMail Question for BukHix

[Kevin_S]

Question on emailing reports

I have an MS Access 2002 fe/ Sql Server be app that tracks employee info. I have numerous reports that I want to have e-mail functionality added to them but the only way I can get this to work is by using a kind of shaddy workaround. Currently I have a combo box that holds the name of the person a user wants to e-mail a report to then on the click event of a command button I run a version of this code:

stDocName = "rptSortEmployTerm"
DoCmd.SendObject acSendReport, stDocName, "Snapshot Format", [Combo4], , , "Attached Report", "Please find the report attached to this email", False
MsgBox "Mail Sent!"

where [Combo4] is the combo box with the persons name and I just paste the persons name in the {To:} section of the send line... Kind a clucky but it works...

NOW - what I would like to do is be able to import /or link the address book to the app so that I can get all of the employees instead of having to add them manually to the combo. The catch is that we are using Novell Groupwise 5.5 so to get Novell and MS Access to talk to one another is not the easiest thing in the world to do....

Any pointers?

Thanks in advance for any/all help on this!
Kevin

 

[BukHix]

I had to do something similar to this a while back but the applications was not GW 5.5 so I'm not sure if this will work in your case

If GW 5.5 has an option to export your address book to a comma delimited file you can setup a little code in Access that will grab the file and automatically add it to a table in Access. Then you can use the table as your combo boxes record source.

I am I on the right track with your question? If not post back and I will try again.

 

[Kevin_S]

Abosolutely on the right track...

As a matter of fact I think the first time I tried to get this to work I tried to incorporate some of your ideas/thoughts on this from a few old posts as your alter ego Talismanic!

Groupwise 5.5 allows address book exports but it only gives you 3 choices for output format:

Novell Address Book - .NAB format
vCard Addresses - .VCF format
All files - (*.*) format

Is this still an option with this version of Groupwise...? I wouldn't be shocked if its not but I hope it is as the method I'm using now works... its just not the bad format ya'know!

Thanks,
kevin

 

[BukHix]

I'm not sure about any of the GW versions. The app I was using was actually a proprietary application that our company was using for HR.

Have you taken a look at what the All files - (*.*) format looks like?

Assuming there is some structure to the file you should be able to get it imported. Although it may be necessary to convert the file, whatever it is by default, to a txt file etc.

Hey I just noticed that you are kinda a neighbor of mine. Well relatively speaking. I spend half my time in Grand Rapids and the other half in Three Rivers. So much so that I am not sure which to call home.

 

[Kevin_S]

Hi Buk - thanks for the reply and sorry to take so long getting back - I went to Kalamazoo this weekend to visit some friends and I wasn't able to get to my e-mail.

O.K. I did export the address book using All Files and I was able to choose the type I wanted to export it as (I choose text file) and the output looks this... (see attachment)

Now - my question is... can I get this to import into a table and... if so... how do i go about setting up the code to do this?

Thanks for your help on this BukHix! I really appreciate it!

In reply to the rest of your post - yes we are a couple of Michiganders aren't we! I'm not sure how the weather is in your area today but we are experiencing a little drizzle this morning and it isn't very pleasent at the moment!

Oh, well... I look forward to hearing from you!
Kev

 

[BukHix]

That is one ugly file. I am not sure whether an import can be automated for that one or not. I think it would take someone much better then I to decipher it though.

 

[mission2java_78]

Can I jump in on this one?

What exactly are you doing on this...you might wanna send this as a tab formatted file or even CHr(13) for each record.

Jon

 

[Kevin_S]

MICHIGAN UNITE!

You gotta love the MI Connection

Hey Jon - What i am trying to do is work around the horrors of Novell GroupWise 5.5 and import an exported addressbook into MS Access so my users can select e-mail address from a combo box and mail out reports to each other. I have a sloppy work-around for this (see first post) but I would like to automate the file import from GroupWise to get a list of all of the potential emails.... instead of having to store these in a table since this info changes quite often - this way the I could set up the db to automatically import the file....

Sounds like it should be fairly simple but the file output is awfully ugly...

What do you think? Is this a possibility?
Kev

 

[WayneRyan]

Kevin,

You can make this work with code by either:

1) Parse the file and insert directly into your Address table
2) Produce a new file by tossing out the first line, removing
the double-quotes, and changing the commas to Tabs.

I have some code that can I can alter to do this. Will watch
and see if other (non-code) solutions arise.

Let me know,
Wayne

 

[mission2java_78]

Yes see Wayne's reply..this is very possible.

Jon

 

[BukHix]

2) Produce a new file by tossing out the first line, removing
the double-quotes, and changing the commas to Tabs.

Getting rid of the first line is where I got stuck. I have never been that great with string manipulation but isn't that how it should be done?

 

[WayneRyan]

Kevin,

Here's a rough sample off the top of my head.
Could be more streamlined with a parsing function,
but the concept is there.

It would probably be easier to just translate the file
into something you could just import, but its a start.

Dim dbs As Database
Dim rst As Recordset
Dim sql As String

Dim strBuf As String
'
' Table Columns
'
Dim TAGMAP As String
Dim FULLNAME As String

Set dbs = CurrentDb
sql = "Select * From AddressBook"
Set rst = dbs.OpenRecordset(sql)

Open "C:\Addressbook.txt" For Input As #1

'
' Throw out the first line ...
'
Line Input #1, strBuf
'
' Read each line and break into fields
'
While Not EOF(1)
   Line Input #1, strBuf
   '
   ' Process TAGMAP
   '
   TAGMAP = Mid(strBuf, 1, InStr(1, strBuf, ","))
   If Len(TAGMAP) = 1 Then
      TAGMAP = ""    ' Null Field
   Else
      TAGMAP = Mid(TAGMAP, 2, Len(TAGMAP) - 2)
   End If  
   strBuf = Mid(strBuf, Instr(1, strBuf, ","), Len(strBuf))
   '
   ' Process Name
   '
   FULLNAME = Mid(strBuf, 1, InStr(1, strBuf, ","))
   If Len(FULLNAME) = 1 Then
      FULLNAME = ""    ' Null Field
   Else
      FULLNAME = Mid(FULLNAME, 2, Len(FULLNAME) - 2)
   End If  
   strBuf = Mid(strBuf, Instr(1, strBuf, ","), Len(strBuf))
   '
   ' Repeat for all other fields
   '
   
   '
   ' Insert into table
   '
   rst.AddNew
   rst!TAGMAP = TAGMAP
   rst!FULLNAME = FULLNAME
   ' Other fields
   rst.Update
   Wend

hth,
Wayne

 

[mission2java_78]

--------------------------------------------------------------------------------Dim iFile As Integer
Dim sData As String

iFile = FreeFile
Open "C:\SomeFile.txt" For Binary Access Read As iFile
sData = Space(LOF(iFile))
Get #iFile, , sData
Close iFile

sData = Mid(sData, Instr(sData, vbCrLf) + 2)
Kill "C:\someFile.txt"

iFile = FreeFile
Open "C:\SomeFile.txt" For Binary Access Write As iFile
Put #iFile, , sData
Close iFile

 

[Kevin_S]

WOW!!!

That is some nice code.... Where would I put something like this? Command button..? Module...? On_Open Event...?

I REALLY, REALLY appreciate the help so far... but I'm a little sheepish to say that I really am not sure where this beautiful code would go (either code from Wayne or Jon)....

I'm being really think today so things are coming a little slow and the code examples are a bit over my head...

Thanks for the help and thanks in advance!
Kev

 

[WayneRyan]

Kevin,

You can use the code in a couple of ways ...

First you could put it on a command button's OnClick event.
But, the input filename would have to be a constant.

Or you could make a pop-up form that would allow you to browse
for a file and use the OnClick of the OK button to run the code.

Jon's code presents another (maybe simpler) option than mine.
He is translating the file. You would implement it also by
either of the two methods above.

Jon, I'm not sure what you are translating the file into.
I thought that the translation would be.
1) Toss out the first line
2) Remove the double quotes
3) Change the commas to Tabs
Then one could just import it.

Wayne

 

[mission2java_78]

Wayne,

My reply was for Buk..he asked how to get rid of the first line.

Thats how.

As for the rest its pretty simple..actually there are 100's of ways to do this. Parsing is kind of boring but its something that almost everyone has to do.

Kevin let me know which route you take.

Jon

 

[Kevin_S]

Hi Buk/Mission/Wayne

Thanks for the help thus far - I feel I'm headed in the right direction with this but I still need some more hand-holding. Wanye - I attempted to add the code you provided to a simple form with a command button - but the code kept giving me type mismatch errors AND the code in the beginning creates a recordset from a table within the db but I can't figure out what you have/or I should have as the required field/field names/ properties in order to be able to have the code import the file and add the records to the table....or am I missing the point somewhere else..?

Also - Are there any commands/procedures/shell that I can call from Access to make GroupWise link to or import from GroupWise without having to export a text file and then import the file? It seems that it would be a little cleaner to have the code pull the records directly from the address book in the software without the whole export/import thing....Any thoughts?

Thanks -Kev

 

[WayneRyan]

Kevin,

I don't know anything about GroupWise, so I can't help you
there.

As for the Import code:

We need to map the columns in your AddressBook table to the
fields in the sample file. If you can post an empty DB with
an empty AddressBook table and your code, I'll take a look.

All of the fields in the Input file are text. I don't see any
that look like numbers, so don't see the Type Mismatch.

Post it and we'll see.

Wayne

 

[Kevin_S]

Wayne - Here is a mock up of the table and Form + Code. I can't attach the actual db where the code will reside as it is too big (too many objects) even when empty. I have taken these from the actual db though.. so it should be all set. My app is in A2002 and SQLServer2000 so the file in the attachment is A2002. I have included the db in 97 as well (In A2002 I have the DA0 3.6 reference library checked so 97 VBA works just as well as 2002.

Thanks for the help....
Kevin

 

[WayneRyan]

Kevin,

Looking good ...

I need to know how to find out the EmployeeID to be used
in the tblAddressBook. I know that I can get it from tblEmployee
but which field in the Import file do I use to look it up?

Can you post the structure of that table?

Wayne