how to modify Novell Groupwise VBA script?

[chemoul]

Hi,
I found Ken Puls' excellent VBA script to send emails from Excel using Novell Groupwise (vbaexpress.com/kb/getarticle.php?kb_id=277).
I'm currently creating a spreadsheet where I will record the name of a large number of document in one column, the date the document should be reviewed in another column, as well as the email address of the person to review the document in a further column.

I am not very familiar with VBA, so I'm wondering how the script could be modified to do the following:
- before sending the emails, go through (Column D or a named range if that's easier) and look up all rows that are on or past a certain review date, and only select the email addresses of these rows
- include the document name (e.g. column E, or a named range if that's easier) of the selected row in the body of the email


Thank you for your help.

[Ken Puls]

Hi there,

Can you post an example workbook of the table structure you see here to do this?

If I understand correctly, you want a table of email addresses, dates and file paths. You're then want to send individual emails to each person that is past a specific date, correct?

[chemoul]

Hi Ken,
thank you for your reply.
Yes, you got it 100% correct.
I have attached a sample spreadsheet. It uses a bit of VBA to automatically fill in today's date and the review date (which is today's date + 365 days). As I said I don't speak VBA unfortantely.

Thank you for your help.

Steve

[Ken Puls]

Hi Steve,

I'll take a look at it tomorrow. I don't have Groupwise at home to test anything, but I think I might have something suitable lying around at work already anyway.

The way I approach what you're after is to:
-Prompt for the date
-Autofilter the table to get that date. (Greater than or equal to on the Review field?)
-Loop through each row of the resulting cells emailing the file

Now... one thing you may need to consider... Is the file path the same to each file, or does it vary? If it varies, I'd put the full file path to the file in the cell. Same for the file type. Always a word doc?

[chemoul]

Regarding sending emails with Groupwise through Excel, I've tested the script you provided earlier and it works fine.

The file paths to the files will vary, so will the document types/file extensions. I don't see this as a big problem though, it would be enough to just output the contens of the cell in the message body.

Thank you so much.

Steve

[Ken Puls]

Actualy, I didn't have anything, so I mocked this up. It's not the most elegant, but I didn't have a ton of time. I would have preferred to avoid creating the Groupwise app every time, but it's effective as is.

You are going to need to:
-Make sure you have a reference set. (Dump this in the exisitng module and you should be fine. Just make sure that eveything I have before the first sub is at the top of the module, and replaces anything you may have before the first sub.)
-Change your mailbox name near the top of the code

Right now it only inserts your filename in the body of the text. If you want to attach the actual file, you'll need to modify your data to be the entire path to the file.

Also, I do no validation checking of the email addresses. It's up to you to make sure that they are right on the worksheet.

[vba]Option Explicit
Private ogwApp As GroupwareTypeLibrary.Application
Private ogwRootAcct As GroupwareTypeLibrary.account

'Enter your mailbox ID here
Private Const sLoginName = "YourMailboxName"
Private Const sMailPassword = vbNullString '(A true password is not required)

Public Sub Email_User()
'Date Created : March 20,2007 13:39
'Author : Ken Puls
'Macro Purpose: Autofilter the table based on date and email each recipient

Dim cl As Range
Dim sEmailTo As String
Dim sSubject As String
Dim sBody As String
Dim sAttachFullPathName As String
Dim varDate As Variant

'Prompt for a date
Do
varDate = InputBox("Please enter a valid date")
If Not IsDate(varDate) Then
If varDate = vbCancel Then Exit Sub
End If
Loop Until IsDate(varDate)

'Extract the range of recipients and email them
With ActiveSheet
With .Range("A1" & .Cells(.Rows.Count, 4).End(xlUp).Row)
.AutoFilter
.AutoFilter Field:=3, Criteria1:=">=" & varDate
For Each cl In Intersect(Columns(1), _
.Offset(1, 0).SpecialCells(xlCellTypeVisible))
If Not cl.Value = vbNullString Then
sEmailTo = cl.Offset(0, 3)
sSubject = "File updated " & cl.Offset(0, 2)
sBody = "Please check the " & cl.Value & " file for updates."

'Uncomment line below if you wish to attach the file. Must be full
'path of workbook to be attached. i.e c:\test\file.xls

'sAttachFullPathName = cl.value

Call Email_Specific_User_Via_Groupwise(sEmailTo, sSubject, _
sBody, sAttachFullPathName)
End If
Next cl
.AutoFilter
End With
End With
End Sub

Private Sub Email_Specific_User_Via_Groupwise(sEmailTo As String, _
StrSubject As String, _
StrBody As String, _
Optional strAttachFullPathName As String, _
Optional sEmailCC As String, _
Optional sEmailBCC As String)

'Date Created : March 20,2007
'Author : Ken Puls
'Macro purpose: To stand as a self contained procedure for creating and
' sending an email via groupwise
'NOTE: You can feed a comma separated string of address to all
' address fields

On Error GoTo EarlyExit

'Required variable declarations
Const NGW$ = "NGW"
Dim ogwNewMessage As GroupwareTypeLibrary.Mail
Dim sLoginName As String, _
sMailPassword As String, _
sCommandOptions As String
Dim aryTo() As String, _
aryCC() As String, _
aryBCC() As String
Dim lAryElement As Long

'Split the emails into an array if necessary
aryTo = Split(sEmailTo, ",")
aryCC = Split(sEmailCC, ",")
aryBCC = Split(sEmailBCC, ",")

'Set application object reference if needed
Application.StatusBar = "Logging in to email account..."
If ogwApp Is Nothing Then
DoEvents
Set ogwApp = CreateObject("NovellGroupWareSession")
DoEvents
End If

'Login to root account if required
If ogwRootAcct Is Nothing Then
If Len(sMailPassword) Then
'Password was passed, so use it
sCommandOptions = "/pwd=" & sMailPassword
Else
'Password was not passed
sCommandOptions = vbNullString
End If
Set ogwRootAcct = ogwApp.Login(sLoginName, sCommandOptions, _
, egwPromptIfNeeded)
DoEvents
End If

'Create new message
Application.StatusBar = "Building email to " & sEmailTo & "..."
Set ogwNewMessage = ogwRootAcct.WorkFolder.Messages.Add _
("GW.MESSAGE.MAIL", egwDraft)
DoEvents

'Assign message properties
With ogwNewMessage
'To field
For lAryElement = 0 To UBound(aryTo())
.Recipients.Add aryTo(lAryElement), NGW, egwTo
Next lAryElement

'CC Field
For lAryElement = 0 To UBound(aryCC())
.Recipients.Add aryCC(lAryElement), NGW, egwCC
Next lAryElement

'BCC Field
For lAryElement = 0 To UBound(aryBCC())
.Recipients.Add aryBCC(lAryElement), NGW, egwBC
Next lAryElement

'Subject & body
.Subject = StrSubject
.BodyText = StrBody

'Attachements (if any)
If Not strAttachFullPathName = vbNullString Then _
.Attachments.Add strAttachFullPathName

'Send the message (Sending may fail if recipients don't resolve)
On Error Resume Next
.Send
DoEvents
If Err.Number = 0 Then Application.StatusBar = "Message sent!" _
Else: Application.StatusBar = "Email to " & sEmailTo & " failed!"
On Error GoTo 0
End With

EarlyExit:
'Release all variables
Set ogwNewMessage = Nothing
Set ogwRootAcct = Nothing
Set ogwApp = Nothing
DoEvents
Application.StatusBar = False
End Sub[/vba]

[chemoul]

Thank you so much for all the work you have put in Ken.Unfortunately no email seems to get sent out.Do I have to create a range again that's named Email_To, instead of Contact email?thank you so much

[Ken Puls]

No... shouldn't do...

I used the file that you sent me, and only changed the email addresses to test it. It doesn't use named ranges at all.

I wonder if the date is giving issue. It shouldn't, but I did notice that your date format is different than mine. Here's what I'd do to check...

I'm assuming that you have very little experience debugging VBA?

Make sure the locals window and debug toolbars are showing. Comment the following line in the first routine:
[vba]
Call Email_Specific_User_Via_Groupwise(sEmailTo, sSubject, _
sBody, sAttachFullPathName)[/vba]

Now, place your cursor in the routine, and start stepping through line by line. (Click the Step Into button on the Debug Toolbar, or keep pressing F8.) You'll start stepping through the code line by line (it will highlight), and you can watch what is happening in the locals window.

Once you've submitted a date, check that it is in your format, and keep stepping. Once the Autofilter is applied, flip back to the worksheet and check if any records are returned by the Autofilter. At any time you want to just let the routine run through, you can either click the Reset button (blue square), or in this case, just press F5 to let it run though. (You may not always want to do either, just so you know.)

Another tool that you may want to use is the Breakpoints. Click just to the left of the code screen on any line, and it should put a red breakpoint there. Now whenever you run the code, it will stop before executing that line.

If the Autofilter seems to be gettin the right records, then the issue is with the Groupwise code portion. Uncomment the lines I gave you and step through the entire code of that routine line by line. Tell me if it ever jumps directly to the EarlyExit portion.

Let me know how you get on.

[chemoul]

Hi,
the locals window seems to display the correct date, however once the auto filter is applied, all data rows in the spreadsheet are gone, all that's left is row1. I have changed 2 of the rows' review date to 19/3/07 so that it would trigger the autofilter.
Any ideas?

[Ken Puls]

Okay, well definitely a date issue then...

Your dates are still in column C, correct?

It should be filtering to include all dates in column C greater than or equal to what you've selected.

[chemoul]

Hi, sorry for my late response, I've been away.
If it's a date issue, what can I do to fix it?

Thanks.

[Ken Puls]

Hi there,

No worries. I haven't been around much laterly either.

Probably the first thing I would do is record a macro using the Autofilter. Pick one of the dates and make sure the list filters correctly. Once that's done, then we should have an idea of exactly what date formats the list is actually being filtered in. (You'll have to forgive me a little on this, as I haven't run in to international date issues myself much.)

I am curious on one thing... did you design the workbook yourself, or do you inherit it from a foreign branch or anything?

Cheers,