Morning,
I have set-up some code that automatically sends an e-mail through Lotus notes according to information on a userform.
It works great, but if Lotus notes is not open then it all goes to hell.
For the life of me I cannot think of a way to get VBA to check whether Lotus notes is already open, and if not to Exit the Sub (or Goto the bottom of the code to do more stuff. I'm sure this is simple, but my excel coding (though getting better) is still mainly finding and bastardising found code for the more complex things.
I have pasted the e-mail sending code below:
'##############################################################################################
' The e-mail sending bit
'##############################################################################################
Dim noSession As Object
Dim noDatabase As Object
Dim noDocument As Object
Dim vaRecipient As Variant
Dim vbRecipient As Variant
Dim rnBody As Variant
Dim Data As DataObject
Dim stuff As Range
Dim stSubject As Variant
Dim cc As Variant
Dim firstname As Variant
Dim surname As Variant
Dim SARA As Variant
Dim Release As Variant
Dim Victim As Variant
Dim Partner As Variant
Dim attachME As Object
Dim Attachment1 As String
Dim Attachment2 As String
Dim attacheME2 As Object
Dim wording As Variant
Set stuff = Worksheets("lists").Range("F3")
'cc = UserForm1.ComboBox1.Value & " admin"
vaRecipient = UserForm1.TextBox4.Value
firstname = UserForm1.TextBox1.Value
surname = UserForm1.TextBox2.Value
stSubject = Worksheets("lists").Range("G3")
On Error Resume Next
Set rnBody = Worksheets("lists").Range("F5")
Set wording = Worksheets("lists").Range("F4")
If UserForm.CheckBox3.Value = False Then
SARA = "SARA Assessment (This should be completed)"
Else
SARA = ""
End If
If UserForm1.CheckBox4.Value = False Then
Release = "HR 1:1 SAR Specific Signed Statement of Agreement"
Else
Release = ""
End If
If UserForm1.CheckBox5.Value = False Then
Victim = "Electronic Handwritten, hard or faxed copies will NOT be accepted"
Else
Victim = ""
End If
If UserForm1.CheckBox6.Value = False Then
Partner = "Electronic Partner Referral (if applicable - please advise either way)"
Else
Partner = ""
End If
If rnBody Is Nothing Then Exit Sub
On Error GoTo 0
Set noSession = CreateObject("notes.notesSession")
Set noDatabase = noSession.GETDATABASE("", "")
If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
Set noDocument = noDatabase.CreateDocument
rnBody.Copy
Set Data = New DataObject
Data.GetFromClipboard
With noDocument
.form = "Memo"
.sendto = vaRecipient
.copyto = cc
.Subject = stSubject & " " & firstname & " " & surname
.body = stuff & " " & firstname & " " & surname & " " & wording & vbCrLf & vbCrLf & "OUTSTANDING PAPERWORK" & vbCrLf & SARA & vbCrLf _
& Release & vbCrLf & Victim & vbCrLf & Partner & vbCrLf & vbCrLf & rnBody
.SaveMessageOnSend = True
End With
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'Attachement Section
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Attachment1 = "S:\Bicester - Talisman House\Programmes\ONE TO ONE PROGRAMME\RESOURCES\Release Statement.doc"
Attachment2 = "S:\Bicester - Talisman House\Programmes\ONE TO ONE PROGRAMME\RESOURCES\WSW referral.doc"
If Attachment1 <> "" Then
Set attachME = noDocument.createrichtextitem("Attachment1")
Set Embedobj1 = attachME.EmbedObject(1454, "", Attachment1, "Attachment")
noDocument.createrichtextitem ("Attachment")
End If
If Attachment2 <> "" Then
Set attachME2 = noDocument.createrichtextitem("Attachment2")
Set Embedobj2 = attachME2.EmbedObject(1454, "", Attachment2, "Attachment3")
noDocument.createrichtextitem ("Attachment3")
End If
'<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
'End of Attachment Section
'<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
With noDocument
.posteddate = Now()
.send 0, vaRecipient
End With
Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing
'AppActivate "Microsoft Excel"
Application.CutCopyMode = False
MsgBox "The e-mail has successfully been created and distributed.", vbInformation
westwing:
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
'End of e-mail bit
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Thanks in advance for any help offered!!!!!
Bookmarks