PDA

View Full Version : Emailing in Excel using VBA Groupwise



columbo1977
10-02-2008, 06:14 AM
Good Afternoon

As part of my job I build / maintain spreadsheets that now use VBA to complete tasks, one of these is emailing a workbook by the pressing of a button to make the user experience easier.

The software used is Excel 2002/2003 and the messages are sent across the company email network and the mail client is GroupWise version 7.0.2

Until now I have had no problem testing the spreadsheets when working on them, I have recently had my pc replaced and now I have a better machine, although I am using the same version software I used before. The only difference that I or the IT department can find is that my machine now has Service Pack 2 for windows and the rest of my team have Service Pack 1 (as did I before my refresh)

Now to the problem :

When I press the buttons on my spreadsheets to email them to myself or any others internally instead of getting to their intended destination, it is sending it to myself as a posted message, it is not leaving my machine at all, so I cannot use any of the buttons I have created.

I might note that a colleague of mine has also tried this on his machine with SP2 and he is having the same problem as me with my sheets as well as ones of his own.

I have also had several colleagues that have SP1 on their machine try and they are having no problems sending.

Please see the below code :



Sub AW_ActiveWorkbook()

'Create a new Workbook from the XXXX and XXXX

Sheets(Array("XXXX1", "XXXX2")).Copy

'Set the temp file and if there is a duplicate filename there delete it

Dim sTemp As String
sTemp = Environ("TEMP")

If Dir$(sTemp & "\NAME.xls") <> "" Then
Kill sTemp & "\ NAME.xls"
End If

'Save the new spreadsheet with password protection

ActiveWorkbook.SaveAs Filename:=sTemp & "\ NAME.xls", FileFormat:= _
xlNormal, Password:="pass", CreateBackup:=False

'Send the email to the recipient

With ActiveWorkbook
.SendMail Recipients:="me@me.co.uk", _
Subject:="New File"
.Close SaveChanges:=False
End With

End Sub


Private Sub CommandButton2_Click()

'This puts a date / time stamp in K13 so that you know when the form was sent
'it then locks the cell

ActiveSheet.Unprotect Password:="XXX"
Range("K13").Select
ActiveCell.Formula = Now()
ActiveCell.Locked = True
ActiveSheet.Protect Password:="XXX"
ActiveWorkbook.Save

Call AW_ActiveWorkbook

End Sub


I am positive this is to do with GroupWise to some degree the IT dept say that the only part of GroupWise that is merged with Windows that might have been updated in SP2 is the windows Messenger Service.

Please say someone can help me with this as If not I am going to have a very big problem when the other machines get upgraded.

Many thanks for looking

Graham