PDA

View Full Version : Check Lotus Notes if Open



gnod
05-30-2007, 08:23 AM
how can i check if lotus notes is open.. thanks..

gnod
05-31-2007, 09:51 AM
i use this code from Ron De Bruin site and modified to my project..
but if lotus is close the file will not attach..


Sub SendMail()
'Working in 97-2007
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set Sourcewb = ActiveWorkbook

'Copy the sheet to a new workbook
With Worksheets("EPCIB")
.Unprotect strPassword
.Copy
.Protect strPassword
End With
Set Destwb = ActiveWorkbook

'Determine the Excel version and file extension/format
With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
'We exit the sub when your answer is NO in the security dialog that you only
'see when you copy a sheet from a xlsm file with macro's disabled.
If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your answer is NO in the security dialog"
Exit Sub
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With

' 'Change all cells in the worksheet to values if you want
' With Destwb.Sheets(1).UsedRange
' .Cells.Copy
' .Cells.PasteSpecial xlPasteValues
' .Cells(1).Select
' End With
' Application.CutCopyMode = False

'Save the new workbook/Mail it/Delete it
TempFilePath = ThisWorkbook.Path & "\"
TempFileName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - EPCIB Uploading" & " " & Format(Now, "dd-mmm-yyyy hh-mm-ss AMPM")

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
.SendMail "", TempFileName
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr

ThisWorkbook.Worksheets("Report").Activate

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub




thanks.

debauch
05-31-2007, 09:57 AM
Dim Maildb As Object 'The mail database



If Maildb.IsOpen = True Then
WasOpen = 1 'Already open for mail
Else
WasOpen = 0
Maildb.OPENMAIL 'This will prompt you for password
End If

gnod
05-31-2007, 10:06 AM
do you have a sample file that will serve as my guide?
sorry but i don't understand.. how does the Maildb it is lotus notes?

debauch
05-31-2007, 10:09 AM
This is everything I declare when using Notes code. It works well for my purpose, which pastes excel data from a userform into a memo and sends it off.


Dim Maildb As Object 'The mail database
Dim UserName As String 'The current users notes name
Dim MailDbName As String 'The current users notes mail database name
Dim maildoc As Object 'The mail document itself
Dim Session As Object 'The notes session
Dim Subject As String 'The subject string
Dim ReturnReceipt As String 'The ReturnReceipt string
Dim Recipient As String 'The Recipient string (or you could use the list)
Dim ccPerson As String
Dim Recip(10) As Variant 'The Recipient list
Dim BodyText As String 'The body text
Dim SaveIt As Boolean 'Save to sent mail
Dim WasOpen As Integer 'Checking to see if the Mail DB was already
Dim ClipBoard As DataObject


Just makes sure the notes references are checked.

gnod
05-31-2007, 11:02 AM
is it possible to see your procedure when you send the data to notes.. thanks..

Charlize
05-31-2007, 11:17 AM
Maybe you can use some of this code. I created this for sending pdf's in a certain directory and using lotus notes. http://www.vbaexpress.com/forum/showthread.php?t=12096

gnod
05-31-2007, 11:28 AM
ok.. i'll try this one.. thanks..

gnod
06-08-2007, 03:54 AM
hi,

i use this code to check if lotus is open and attach the excel in lotus mail but how can i attach the file after the user is log-in to the lotus notes..


Sub SendMail()
'Working in 97-2007
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String

CheckLotusApp

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set Sourcewb = ActiveWorkbook

'Copy the sheet to a new workbook
With Worksheets("EPCIB")
.Unprotect strPassword
.Copy
.Protect strPassword
End With
Set Destwb = ActiveWorkbook

'Determine the Excel version and file extension/format
With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
'We exit the sub when your answer is NO in the security dialog that you only
'see when you copy a sheet from a xlsm file with macro's disabled.
If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your answer is NO in the security dialog"
Exit Sub
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With

' 'Change all cells in the worksheet to values if you want
' With Destwb.Sheets(1).UsedRange
' .Cells.Copy
' .Cells.PasteSpecial xlPasteValues
' .Cells(1).Select
' End With
' Application.CutCopyMode = False

'Save the new workbook/Mail it/Delete it
TempFilePath = ThisWorkbook.Path & "\"
TempFileName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " - EPCIB Uploading" & " " & Format(Now, "dd-mmm-yyyy hh-mm-ss AMPM")

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
.SendMail "", TempFileName, True
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr

ThisWorkbook.Worksheets("Report").Activate

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Sub CheckLotusApp()
Dim strPathLotusNotes As String

On Error GoTo call_app
AppActivate "Lotus Notes"
On Error GoTo 0
Exit Sub

call_app:
strPathLotusNotes = "c:\lotus\notes\notes.exe"
If Dir(strPathLotusNotes) = "" Then
strPathLotusNotes = Application.GetOpenFilename(FileFilter:="Executables, *.exe", _
Title:="Notes.exe not found in C:\Lotus\Notes\.")
Shell strPathLotusNotes, vbNormalFocus
Else
Shell strPathLotusNotes, vbNormalFocus
End If
End Sub


thanks..