Free Microsoft Excel 2013 Quick Reference

Sending Excel through email using Lotus Notes

Hi everybody,

This is part of the code for sending email from Excel. I obtained this from Ron De Bruin.

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = "ron@debruin.nl"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add Destwb.FullName
'You can add other files also like this
'.Attachments.Add ("C:test.txt")
.Send 'or use .Display
End With
On Error GoTo 0
.Close SaveChanges:=False
End With

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

Set OutMail = Nothing
Set OutApp = Nothing

I think the code won't work with Lotus Notes. Can anyone help me with this?


The code below sends out an e-mail through microsoft excel using lotus notes 8 but it does not keep the text formatted. For instance there are headings in the text that i want to be bold but it doesn't keep the bold formatting. I have tried a couple of things but it seems a lot of the things (like htmlbody instead of body) only seem to work with Lotus Notes 7 and not 8. Any help would be greatly appreciated. Or if there is another way to make a form in excel and then have it get sent out (in the correct format) through lotus notes 8, I would like to know.

Lots of googling has lead me to this site because I can't seem to figure it out and neither can anyone (it seems) at my company.

Thanks

Sub
Picture5_Click()

Dim noSession As Object, noDatabase As Object, noDocument As Object
Dim obAttachment As Object, EmbedObject As Object
Dim stSubject As Variant, stAttachment As String
Dim vaRecipient As Variant, vaMsg As Variant, vaSubject As Variant, vaCopyTo As Variant

Set noSession = CreateObject("Notes.Notessession")
Set noDatabase = noSession.GetDataBase("", "")
'If Lotus Notes is not open then open the mail-part of it.
If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
Set noDocument = noDatabase.CreateDocument

'vaRecipient = Worksheets("Sheet1").Range("D11")
vaRecipient = Split(Range("D11").Value, ",")
vaCopyTo = Split(Range("D16").Value, ",")

vaSubject = Worksheets("Sheet1").Range("D21")
'What was the problem reading

vaMsg = Worksheets("Sheet1").Range("C23") & vbCrLf &
Worksheets("Sheet1").Range("C277") & Worksheets("Sheet1").Range("C24")

'Where did the outage.... reading
vaMsg = vaMsg & vbCrLf & vbCrLf & vbCrLf & Worksheets("Sheet1").Range("C31") & vbCrLf
& Worksheets("Sheet1").Range("C277") & Worksheets("Sheet1").Range("C32")

'Who was impacted.... reading
vaMsg = vaMsg & vbCrLf & vbCrLf & vbCrLf & Worksheets("Sheet1").Range("C39") & vbCrLf
& Worksheets("Sheet1").Range("C277") & Worksheets("Sheet1").Range("C40")

'When did the outage / incident occur.... reading
vaMsg = vaMsg & vbCrLf & vbCrLf & vbCrLf & Worksheets("Sheet1").Range("C47") & vbCrLf
& Worksheets("Sheet1").Range("C277") & Worksheets("Sheet1").Range("C48")

'resolution.... reading
vaMsg = vaMsg & vbCrLf & vbCrLf & vbCrLf & Worksheets("Sheet1").Range("C50") & vbCrLf
& Worksheets("Sheet1").Range("C277") & Worksheets("Sheet1").Range("C51")

'next steps.... reading
vaMsg = vaMsg & vbCrLf & vbCrLf & vbCrLf & Worksheets("Sheet1").Range("C58") & vbCrLf
& Worksheets("Sheet1").Range("C277") & Worksheets("Sheet1").Range("C59")

noDocument.SendTo = vaRecipient

noDocument.CopyTo = vaCopyTo
noDocument.Form = "Memo"
noDocument.SendTo = vaRecipient
noDocument.CopyTo = vaCopyTo
noDocument.Subject = vaSubject
noDocument.body = vaMsg
noDocument.SaveMessageOnSend = True

With noDocument
.PostedDate = Now()
.SEND 0, vaRecipient
End With

Set EmbedObject = Nothing
Set obAttachment = Nothing
Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing

'Activate Excel for the user.
AppActivate "Microsoft Excel"
MsgBox "The e-mail has successfully been created and distributed.", vbInformation

End Sub


Hi all,

I am writing an application that presents a form (on a sheet called Form) to the user. This form has multiple fields for entry, (some fill-in and some choices via pull-down menus in combo boxes).

When the user clicks submit, the data is all moved, formatted, calculated as needed, and inserted into a seperate sheet, called Database. There is also a 'Clear Form' button that does just that.

Everything as far as functionality with the above features I can get to work just fine. My problem is this:

I have been informed that an email needs to be sent to an administrator whenever the user clicks submit. Normally, this would be simple, but there's more. The user already specifies his/her company division in the cell "C9". This is a pull-down menu that is populated with data from a table that is hidden off to the side of the form. This table includes each of the possible divisions (there are 13), as well as the administrator email address that a message needs to be sent to when the form is submitted.

What I need is some code to do is this:

1) Given the choice for the user's division, determine which email address needs to receive an email. [email address are in Range("R6:R18")]
2) Generate an email containing the following contents:
Subject:
Global Validation Event Approval

Message:
("C6") has submitted an event to the Global Validation Event Database on ("C13"). Please review the submission and approve or decline the event in the master Database. Then, inform ("C6") of the updated status of the event. Thank you.

3) Send the email (using lotus notes) to the address determine in step 1.

I figured that I could use some form of the LOOKUP function, but I cannot seem to get this to work. Also, I am unsure as to how to get excel to send the email through Lotus Notes. I have seen code for Outlook, but not for Lotus...so I am a bit of a beginner as far as that is concerned.

Please let me know if and how this would be possible.

THANK YOU IN ADVANCE!!!!

I am using the following code

Set Session = CreateObject("Lotus.NotesSession")
Session.Initialize (PWD)
et Directory = Session.GetDbDirectory(Session.ServerName)
Set MailDatabase = Directory.OpenMailDatabase()
Set Document1 = MailDatabase.CreateDocument()
....

It works on all my users machines, apart from 1. He is getting the following error message on the
second line above

Could not open the ID file.

We are using Lotus Notes with the userid files on a server.

Any ideas, anyone?

Nirmal

For reference, this question is a product of my previous one at http://www.mrexcel.com/board2/viewtopic.php?t=59878

There is code in that thread that I am using to automatically populate one sheet with information that is entered in another. Which is working quite well.

However.

The numbers it captures for the sheet, I also have to send in an email (using Lotus Notes) to someone. I don't need an attachment, I just need the text that was just entered in the form via macro (does that make sense?).

I snagged the code from this thread ( http://www.mrexcel.com/board2/viewto...ht=lotus+notes ), thinking it sounded kind of like what I was wanting to do, and I could maybe figure something out.

But, no. I am completely and utterly confused by it. Nothing new.

I already have the first part of the email text entered, but then in the middle goes the numbers, then more email text at the bottom (I send the exact same email every week, save the numbers).

How can I take out the stuff that makes it an attachment and just copy the text from column B (starting from B5 down--and only the used cells) and just paste it in the middle of the email?

Or can I? No need to worry about saving the sheet, either.

Any thoughts appreciated, as usual--and I'll try to answer any questions as best as I can

Hiya,

Is this possible - to use the kind of formulas created by Ron de Bruin but using Lotus Notes as the primary client to send the email?

If not is there a cheap workaround maybe using mailto: ?

Look to the bottom for the final code...

I have a multi-sheet Excel workbook that, using macros, I can email (via Lotus Notes) to whomever I choose. However, I cannot figure out how to send only 1 of the sheets in this workbook via Lotus Notes. I think I must have to do something whereby I copy the sheet to a new workbook and then send that new workbook, but I can't figure out the code. Any ideas???

Want to know if there is a way in excel that when you go to file, then send,
to send email as attachment that exel uses lotus note by default

I currently use the following code - with no problems at all, and it emails to the recipients listed ok. But i would like a pop up box with a selection of managers - which allow the user to select just one, so that the manager gets a copy too.

Is this at all possible......

Sub SendWithLotus()

    Dim noSession As Object, noDatabase As Object, noDocument As Object
    Dim obAttachment As Object, EmbedObject As Object
    Dim stSubject As Variant, stAttachment As String
    Dim vaRecipient(2) As Variant, vaMsg As Variant

    Const EMBED_ATTACHMENT As Long = 1454

   'Get the name of the recipient from the user.
    Do
    vaRecipient(0) = "mail1@email.com"
    vaRecipient(1) = "mail2@email.com"
    vaRecipient(2) = "mail3@email.com"
    Loop While vaRecipient(2) = ""

    'If the user has canceled the operation.
    If vaRecipient(2) = False Then Exit Sub

    'Get the message from the user.
    Do
    vaMsg = ActiveWorkbook.Name
    Loop While vaMsg = ""

    'If the user has canceled the operation.
    If vaMsg = False Then Exit Sub

    'Add the subject to the outgoing e-mail which also can be retrieved from the users
    'in a similar way as above.

    stSubject = ActiveWorkbook.Name
    
    'Retrieve the path and filename of the active workbook.
    stAttachment = ActiveWorkbook.FullName

    'Instantiate the Lotus Notes COM's Objects.
    Set noSession = CreateObject("Notes.NotesSession")
    Set noDatabase = noSession.GETDATABASE("", "")

    'If Lotus Notes is not open then open the mail-part of it.
    If noDatabase.IsOpen = False Then noDatabase.OPENMAIL

    'Create the e-mail and the attachment.
    Set noDocument = noDatabase.CreateDocument
    Set obAttachment = noDocument.CreateRichTextItem("stAttachment")
    Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment)

    'Add values to the created e-mail main properties.
    With noDocument
        .Form = "Memo"
        .SendTo = vaRecipient
        .Subject = stSubject
        .Body = vaMsg
        .SaveMessageOnSend = True
    End With

    'Send the e-mail.
    With noDocument
        .PostedDate = Now()
        .Send 0, vaRecipient
    End With

    'Release objects from the memory.
    Set EmbedObject = Nothing
    Set obAttachment = Nothing
    Set noDocument = Nothing
    Set noDatabase = Nothing
    Set noSession = Nothing

    'Activate Excel for the user.
    AppActivate "Microsoft Excel"
    MsgBox "The e-mail has successfully been created and is ready for you to replicate.", vbInformation
End Sub
Regards JonesZoid

Hello new guy to forum and VBA for that matter... but need some help...

Ok, So I am trying to creat a looping code that will grab emails from 1 list and populate them in my code that I use to send auto send emails in lotus notes, and then pick the attachment location from another list in excel... So essentially I am looking to send 1 specific file (pdf's) to a specific person... than loop to pick the next person in the list and the next location... so the end result would be that I have sent 10 emails with attachments to 10 different recipients with a unique file attached to each. Below is my code that I sue to attach and send emails from excel through Lotus notes...

'Starting to build email
Dim oSess As Object
Dim oDB As Object
Dim oDoc As Object
Dim oItem As Object
Dim direct As Object
Dim Var As Variant
Dim flag As Boolean

Set oSess = CreateObject("Notes.NotesSession")
Set oDB = oSess.GETDATABASE("", "")
Call oDB.OPENMAIL
flag = True
If Not (oDB.IsOpen) Then flag = oDB.Open("", "")

If Not flag Then
MsgBox "Can't open mail file: " & oDB.SERVER & " " & oDB.FILEPATH
GoTo exit_SendAttachment
End If
On Error GoTo err_handler

'Building Message
Set oDoc = oDB.CREATEDOCUMENT
Set oItem = oDoc.CREATERICHTEXTITEM("BODY")
oDoc.Form = "Memo"
oDoc.Subject = "Budget File"
oDoc.sendto = "example@example.com"
oDoc.body = "whatever I would like to type same message for all recipients."
oDoc.postdate = Date
oDoc.SaveMessageOnSend = True

'Attaching DATABASE
Call oItem.EmbedObject(1454, "", "H:example.pdf")
oDoc.visable = True
'Sending Message
oDoc.SEND False
exit_SendAttachment:
On Error Resume Next
Set oSess = Nothing
Set oDB = Nothing
Set oDoc = Nothing
Set oItem = Nothing
'Done
Exit Sub
err_handler:
If Err.Number = 7225 Then
MsgBox "File doesn't exist"
Else
MsgBox Err.Number & " " & Err.Description
End If
On Error GoTo exit_SendAttachment

End Sub

I am trying to send en email via Lotus Notes using VBA (in Access) but I am unable to get the email to send from a different mail database other than my personal one.

I have tried to explicitly enter the ServerName and DatabaseName in the following section


	VB:
	
 Maildb = session.GETDATABASE("ServerName", MailDbName) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The below excerpt displays the entire code which still sends via my personal Mail Inbox:


	VB:
	
 _ 
    String, bodytext As String, SaveIt As Boolean) As Boolean 
    Dim Maildb        As Object 
    Dim MailDbName    As String 
    Dim MailDoc       As Object 
    Dim AttachME      As Object 
    Dim session       As Object 
    Dim EmbedObj      As Object 
     
    On Error Goto err_SendNotesMail 
     
     ' Start a session to notes
    Set session = CreateObject("Notes.NotesSession") 
     
     ' Get the sessions username and then calculate the mail file name
     ' You may or may not need this as for MailDBname with some systems you
     ' can pass an empty string
    MailDbName = "MyDatabaseName.nsf" 
     
     ' Open the mail database in notes
    Set Maildb = session.GETDATABASE("ServerName", MailDbName) 
    If Maildb.IsOpen = True Then 
         'Already open for mail
    Else 
        Maildb.OpenMail 
    End If 
     
     ' Set up the new mail document
    Set MailDoc = Maildb.CreateDocument 
    MailDoc.Form = "Memo" 
    MailDoc.sendTo = recipient 
    MailDoc.Subject = Subject 
    MailDoc.Body = bodytext 
    MailDoc.SaveMessageOnSend = SaveIt 
     
     ' Set up the embedded object and attachment and attach it
    If attachment  "" And Dir(attachment)  "" Then 
        Set AttachME = MailDoc.CreateRichTextItem("Attachment") 
        Set EmbedObj = AttachME.EmbedObject(1454, "", attachment, "Attachment") 
         'MailDoc.CREATERICHTEXTITEM ("Attachment")
    End If 
     
     ' Send the document
    MailDoc.Send 0, recipient 
    Maildb.Close 
     ' Clean Up
    Set Maildb = Nothing 
    Set MailDoc = Nothing 
    Set AttachME = Nothing 
    Set session = Nothing 
    Set EmbedObj = Nothing 
    SendMail = True 
     
end_SendNotesMail: 
    Exit Function 
     
err_SendNotesMail: 
    Select Case Err.Number 
Case 429: 
        MsgBox "Error: " & vbCrLf & Err.Description & vbCrLf & "Possible " & _ 
        "cause: Lotus Notes not installed", vbCritical, "Error while initializing LotusNotes" 
Case Else: 
        MsgBox Err.Description & Err.Number, vbCritical, "Error Lotus Notes" 
    End Select 
    Resume end_SendNotesMail 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi folks,

I've got a problem which is quite complex and I know little or nothing from VBA so...

At work we use an excel sheet for our waste water, and instead of having to copy the
excel file to a stationary email in lotus notes on a shared email account, I was hoping I could add a button which does that trick for me.

So, the excel sheet is just one sheet, called "Sloot Silo2.XLS".
The entire workbook should be added to the email as an attachment.

We all can acces a shared email account in lotus notes, which is called LABO Sluiskil,
located on server APP02/Srv/Yara with the filename GMAILya001009.nsf.

and the stationary email has a subject line of Sloot Silo2 segm4.

Is there any way to do this ?
If it could open an email, attach the file and add the "to", "CC" subject line and body message (which can be stored inside the script I suppose) I would be happy too.

If it could simply attach the file to this stationary it would be even easier.

However it would also be needed that the script only sets the email ready to send,
but that the user still has to press the send button so that he can oversee the thing and check
for possible mistakes he made.

Quite a thing to tackle, and I wouldnt be surprised if this wouldn't be possible or if nobody knows how to do this.

anyways: thanks for trying !

kind regards

Hi!

I hope someone can help me...and make it easily understood. I'm such a newbie.

I am using Excel 2010 32bit & Lotus Notes 8.5.

I have an Excel 2010 list with columns named:

Institution Name (A2:A158)
Code (B2:B158)
email address (C2:C158)
location the word attachment is located (D2:D158)

I have a Lotus Notes stationary letter already set up in Lotus, all it needs is the appropriate merge info from the Excel list inserted.

I want to be able to do the following automatically:

- Open the aforementioned stationary.
- Enter the email address from C2.
- Insert the code from B2 & the institution name from A2 into the body of the email in their appropriate spots.
- Attach the corresponding Word document for that email.
- Automatically send with a copy going into the sent folder.
- Have Lotus open the same stationary again and do the steps above only this time, it would be the next line of information and the corresponding Word document.

Does this sound possible??

Any help provided would be much appreciated!

Thank you so much!
Syn

Send cell range to the body of a Lotus Notes email (would like a button do do this (only if its straight forward to do)
Hi all,

Name is Rob from Oz....I have a tricky one and hope that some one can assist.

I would like to have data from a line entry and or cell within Excel to be sent as an email using Lotus Notes.

I have limited programming knowledge so please keep it simple.

I am desperate please help

Rob
emailori0405@gmail.com

I would like to assign a macro button to an Excel spreadsheet that when
clicked will open up email in Lotus Notes 8.5.1 with the name of the Excel
spreadsheet as an attached file in the Lotus Notes email. The person that
opens the spreadsheet via email should be able to make changes to the
spreadsheet and click that same macro button and forward it on to the next
person without saving the spreadsheet to their hard drive. Is this possible
and if so, can you please provide me with the code. I am not a programmer so
any help that you can provide would be greatly appreciated.

I have some excel vba code that sends emails via Lotus Notes and works great. However, I have a personal mailbox and a group mailbox. The code currently sends the mails from my personal address, but I need to find a way to send via the group mailbox (e.g. group@mywork.com). This way, the recipient does not see my name, but sees the email came from a group box, which is shared by several of my colleagues at work.

After scanning this and other forums for some time, I have seen others ask this question, but have never seen a solution offered. Does anyone have any code that would work here?

Thank you!

Hi All,

I need some help in sending email through Lotus notes based on a criteria.Any Help would be greatly appericated.

I have got code to send the email from Lotus notes from the same Forum but iam illeterate to add condtions to it.Iam going to explain below with attachements.

Here is the Code which can send emails through the lotus notes and it will not add attachments.I need to add attachments based on condtions.

Public Sub EmailCode()

Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDBName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " ")))
Set Maildb = Session.GETDATABASE("", MailDBName)
If Maildb.IsOpen <> True Then
On Error Resume Next
Maildb.OPENMAIL
End If

Set MailDoc = Maildb.CreateDocument
MailDoc.form = "Memo"

With MailDoc
.Sendto = "shekar.goud@gmail.com"
.Copyto = ""
.blindcopyto = ""
.Subject = "This is a test of emailing in Lotus through Excel VBA"
.Body = "Success!"
End With

MailDoc.SaveMessageOnSend = True

MailDoc.posteddate = Now()

MailDoc.Send 0, "shekar.goud@gmail.com"

End Sub

1)I need to send emails through the Lotus notes with attachments.

iam attaching the sample data and email formats in the other excel sheet.

Iam working for an asset management and in my work i have to collect all the rejects data from various people which will come in an single sheet (attached sample data file.xlsx) sample sheet.

I Then have to segregate on the basis of company in column AP in Sample Sheet in the Sample Data file i have segregated in different sheets for example.

one condition there for UK assets if column AP=RIN and column AH=UK i have to make it separate excel sheet and has to send to the concern person.

Iam attaching the rejects file in which the format has to send is there.

Any Help is Greatly Appreciated.

Thanks & Regards,
Thonta Shekar.

Hi

I have a 100 odd files to be sent to 100 recipients all in one folder.
(C:temp)
I have a list of 100 email ids in column A and the file name (mentioned
above) in column B in a file called names.xls

I want a code that will send out each of these files to the recipients
as per the list in name.xls until it finishes all the names in the
file.

The hard part is I use Lotus notes 6.5 for mails...
Can this be done in lotus environment?
Any help is welcome

Thanks
Karthik Bhat

Hi Guys
I require Please help me with this macro.

This macro creates an email in lotus notes, attaching a cell range, you can edit information and add addresses in Lotus Notes program.

What I require is that instead of attaching the cell range I attach a file. Pdf that generates macro myself.

I hope I can help.

Thanks

Option Explicit
 
'Function for finding the first top level window in the windows list
'that meet the criteria.
Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
      (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
 
 
Sub Send_Formatted_Range_Data()
   Dim oWorkSpace As Object, oUIDoc As Object
   Dim rnBody As Range
   Dim lnRetVal As Long
 
   Const stTo As String = "Excel@Microsoft.com"
   Const stCC As String = "Lotus Notes@IBM.com"
   Const stBody As String = vbCrLf & "As per agreement." & vbCrLf _
         & "Kind regards" & vbCrLf & "Dennis"
   Const stSubject As String = "Report xxx"
   Const stMsg As String = "An e-mail has been succesfully created and saved."
 
   'Check if Lotus Notes is open or not.
   lnRetVal = FindWindow("NOTES", vbNullString)
 
   If lnRetVal = 0 Then
      MsgBox "Please make sure that Lotus Notes is open!", vbExclamation
      Exit Sub
   End If
 
   Application.ScreenUpdating = False
 
   'A named range in the activesheet is in use.
   Set rnBody = ActiveSheet.Range("Report")
   rnBody.Copy
 
   'Instantiate the Lotus Notes COM's objects.
   Set oWorkSpace = CreateObject("Notes.NotesUIWorkspace")
 
   On Error Resume Next
   Set oUIDoc = oWorkSpace.ComposeDocument("", "mailxldennis.nsf", "Memo")
   On Error GoTo 0
 
   Set oUIDoc = oWorkSpace.CurrentDocument
 
   'Using LotusScript to create the e-mail.
   Call oUIDoc.FieldSetText("EnterSendTo", stTo)
   Call oUIDoc.FieldSetText("EnterCopyTo", stCC)
   Call oUIDoc.FieldSetText("Subject", stSubject)

   'If You experience any issues with the above three lines then replace it with:
   'Call oUIDoc.FieldAppendText("EnterSendTo", stTo)
   'Call oUIDoc.FieldAppendText("EnterCopyTo", stCC)

   'Call oUIDoc.FieldAppendText("Subject", stSubject)
   
   'The can be used if You want to add a message into the created document.
   Call oUIDoc.FieldAppendText("Body", vbNewLine & stBody)
 
   'Here the selected range is pasted into the body of the outgoing e-mail.
   Call oUIDoc.GoToField("Body")
   Call oUIDoc.Paste

   'Save the created document.
   Call oUIDoc.Save(True, False, False)
   'If the e-mail also should be sent then add the following line.
   'Call oUIDoc.Send(True)
 
   'Release objects from memory.
   Set oWorkSpace = Nothing
   Set oUIDoc = Nothing
 
   With Application
      .CutCopyMode = False
      .ScreenUpdating = True
   End With
 
   MsgBox stMsg, vbInformation
 
   'Activate Lotus Notes.
   AppActivate ("Notes")
 
End Sub


Hey, I hope this is the right place to post this...

Basically I'm putting hyperlinks into an Excel spreadsheet (using Excel 2002). They link to a different file in a shared network drive. However, when the file is sent as an e-mail, and opened by someone else, the file path has changed to the user's local C: drive. I've tried to set the hyperlink base to the folder path, and zipping the file before sending, but it still isn't working properly. I'm using Lotus Notes to send the files.

Any ideas on locking the file path so that it will still retain the proper file path when it is e-mailed?

Thanks.

****sorry about this, just got the problem fixed, changing the base worked. This thread can be deleted*****

I'd like to have VBA to send out an e-mail when something occurs. If my
company used Microsoft Exchange I gather this would be no problem. But we
use Lotus Notes. Is it possible for me to send an e-mail? (We are using
Office XP.)

Don <donwiss at panix.com>.

I am trying to use the FollowHyperlink method to generate an email using the
default client. Works with Outlook, but not with Lotus Notes; it activates
Lotus Notes, but does not initiate an email message.

To test, I created a simplified version of the same routine in another
workbook and this second program DID work!

This may be a "forest for the trees" issue, but can anyone point out the
*significant* differences between the two routines that would prevent the
first from working? (I mean, besides the first set of nested IF Then...Else
statements at the start of the first routine)

Thanks in advance
- Glenn Ray

========First Routine (not working) ===========
'This is a Click event inside a user form (frmRejectEmail)
Option Explicit
Dim stext, sAddedtext As String

Private Sub btnSend_Click()
frmRejectEmail.Hide
boolRej = True
On Error GoTo Err_Send_Click

If frmRejectEmail.chSuprCC.Value = True Then
If Range("SuprEmail").Value = Range("UserEmail").Value Then
sAddedtext = "&BCC=" & Range("SuprEmail").Value
Else
sAddedtext = "&CC=" & Range("SuprEmail").Value
sAddedtext = sAddedtext & "&BCC=" & Range("UserEmail").Value
End If
End If
sAddedtext = sAddedtext & "&Subject=" & frmRejectEmail.lblSubject.Caption
sAddedtext = sAddedtext & "&Body=" & frmRejectEmail.lblDefaultMsg.Caption
If frmRejectEmail.txtMsgBody.Value <> "" Then
sAddedtext = sAddedtext & " - " & frmRejectEmail.txtMsgBody.Value
End If

stext = "mailto:" & strEmail
Mid$(sAddedtext, 1, 1) = "?"
stext = stext & sAddedtext

ThisWorkbook.FollowHyperlink stext

Exit_Send_Click:
Exit Sub

Err_Send_Click:
MsgBox Err.Description & vbLf & "Contact Glenn Ray for assistance."
Resume Exit_Send_Click

End Sub

=======Second routine (works)==============
Option Explicit
Dim strtext As String
Dim chSuprCC As Boolean
Const strMsgBody as String = "And another thing..."
Sub Send_mail2()
On Error GoTo Err_Send_Click
chSuprCC = True
Dim stext As String
Dim sAddedtext As String
If chSuprCC = True Then
sAddedtext = "&BCC=" & "jdoe@company.com"
End If
sAddedtext = sAddedtext & "&Subject=" & "this is the subject"
sAddedtext = sAddedtext & "&Body=" & "you messed up and this is what
you're gonna do about it"
If strMsgBody <> "" Then
sAddedtext = sAddedtext & " - " & vbCrLf & vbCrLf & strMsgBody
End If

stext = "mailto:" & "jdoe@company.com"
Mid$(sAddedtext, 1, 1) = "?"
stext = stext & sAddedtext

ThisWorkbook.FollowHyperlink stext

Exit_Send_Click:
Exit Sub

Err_Send_Click:
MsgBox Err.Description
Resume Exit_Send_Click

End Sub

I want to zip an Excel file before sending it as attachment using Lotus Notes. I got this code from http://www.rondebruin.nl/windowsxpzip.htm and I mix it up with some other codes to achieve this but it doesnt seem to work.

Sub NewZip(sPath)
'Create empty Zip File
'Changed by keepITcool Dec-12-2005
    If Len(Dir(sPath)) > 0 Then Kill sPath
    Open sPath For Output As #1
    Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
    Close #1
End Sub

Sub Zip_Mail_ActiveWorkbook()
    Dim strDate As String, DefPath As String, strbody As String
    Dim oApp As Object, OutApp As Object, OutMail As Object
    Dim FileNameZip, FileNameXls
 
    DefPath = Application.DefaultFilePath
    If Right(DefPath, 1) <> "" Then
        DefPath = DefPath & ""
    End If
 
    'Create date/time string and the temporary xls/zip file names
    strDate = Format(Now, " dd-mmm-yy h-mm-ss")
    FileNameZip = DefPath & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & strDate & ".zip"
    FileNameXls = DefPath & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & strDate & ".xls"
 
    If Dir(FileNameZip) = "" And Dir(FileNameXls) = "" Then

        'Make copy of the activeworkbook
        ActiveWorkbook.SaveCopyAs FileNameXls
 
        'Create empty Zip File
        NewZip (FileNameZip)
 
        'Copy the file in the compressed folder
        Set oApp = CreateObject("Shell.Application")
        oApp.NameSpace(FileNameZip).CopyHere FileNameXls
 
        'Keep script waiting until Compressing is done
       On Error Resume Next
        Do Until oApp.NameSpace(FileNameZip).items.Count = 1
            Application.Wait (Now + TimeValue("0:00:01"))
        Loop
       On Error GoTo 0
 
        'Create the mail
            Set OutApp = CreateObject("Notes.NotesSession")
    Set Maildb = OutApp.GETDATABASE("", "")
    
    If Maildb.IsOpen = True Then
        WasOpen = 1 'Already open for mail
    Else
        WasOpen = 0
        Call Maildb.OPENMAIL 'This will prompt you for password
    End If
     
    On Error GoTo err_handler
    
    Set OutMail = Maildb.CreateDocument
    
    Const EMBED_ATTACHMENT As Long = 1454
    Const stTitle As String = "Active workbook status"
    Const stMsg As String = "The active workbook must first be saved " & vbCrLf _
    & "before it can be sent as an attachment."
     'Check if the active workbook is saved or not
     'If the active workbook has not been saved at all.
    If Len(ActiveWorkbook.Path) = 0 Then
        MsgBox stMsg, vbInformation, stTitle
        Exit Sub
    End If
     'If the changes in the active workbook have been saved or not.
    If ActiveWorkbook.Saved = False Then
        If MsgBox("Do you want to save the changes before sending?", _
        vbYesNo + vbInformation, stTitle) = vbYes Then _
        ActiveWorkbook.Save
    End If
    
    stAttachment = ActiveWorkbook.FullName
    'Create the e-mail and the attachment.
    Set OutMail = noDatabase.CreateDocument
    Set obAttachment = OutMail.CreateRichTextItem("stAttachment")
    Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment)
    
            With OutMail
            .Form = "Memo"
            .SendTo = "anyone@anywhere"
            .copyto = ""
            .blindcopyto = ""
            .subject = ""
            .Postdate = Date
            .SaveMessageOnSend = True
            'If you like to add message to your mail
            '.Body = "Hi there"

        End With
        On Error GoTo 0
    
    
    'To view mail before sending
    Set workspace = CreateObject("Notes.NotesUIWorkspace")
    Call workspace.EDITDOCUMENT(True, OutMail).GOTOFIELD("Body")
    
        Set OutMail = Nothing
        Set OutApp = Nothing
        Set oApp = Nothing
 
        'Delete the temporary xls file and zip file you send
        Kill FileNameZip
        Kill FileNameXls
    Else
        MsgBox "FileNameZip or/and FileNameXls exist"
    End If
    
exit_SendAttachment:
    On Error Resume Next
    Set Maildb = Nothing
    Set MailDoc = Nothing
     
     ' Done
    Exit Sub
     
err_handler:
    emailErr = True
    If Err.Number = 7225 Then
        MsgBox "File doesn't exist"
    Else
        MsgBox Err.Number & " " & Err.Description
    End If
    On Error GoTo exit_SendAttachment

End Sub
I think the problem is caused by
    Const EMBED_ATTACHMENT As Long = 1454
    Const stTitle As String = "Active workbook status"
    Const stMsg As String = "The active workbook must first be saved " & vbCrLf _
    & "before it can be sent as an attachment."
     'Check if the active workbook is saved or not
     'If the active workbook has not been saved at all.
    If Len(ActiveWorkbook.Path) = 0 Then
        MsgBox stMsg, vbInformation, stTitle
        Exit Sub
    End If
     'If the changes in the active workbook have been saved or not.
    If ActiveWorkbook.Saved = False Then
        If MsgBox("Do you want to save the changes before sending?", _
        vbYesNo + vbInformation, stTitle) = vbYes Then _
        ActiveWorkbook.Save
    End If
    
    stAttachment = ActiveWorkbook.FullName
    'Create the e-mail and the attachment.
    Set OutMail = noDatabase.CreateDocument
    Set obAttachment = OutMail.CreateRichTextItem("stAttachment")
    Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment)
I hope somebody can help me with this

I pieced together the following code to Send an Email in Lotus Notes to a column of recipients from my spreadsheet. It saves the email in the drafts folder for review. I thought someone could use the code.
Sub Create_Draft()

Dim NotesDb                 As Object
Dim NotesDoc                As Object
Dim NotesRTF                As Object
Dim NotesSession            As Object
Dim UserName                As String
Dim MailDbName              As String
Dim MyAttachment            As String
Dim SendToRecip             As Variant
Dim CopyToRecip             As Variant
Dim BlindCopyToRecip        As Variant
Dim MyName                  As Variant
Dim richStyle               As Variant
   'Variables for Excel.
  Dim wbBook As Workbook
  Dim wsSheet As Worksheet
  Dim lnLastRow As Long
  Application.ScreenUpdating = False
  Set wbBook = ThisWorkbook
    
    'Retrieve the SendTo of recipients.
    With wsSheet
      lnLastRow = Cells(Rows.Count, "H").End(xlUp).Row
      SendToRecip = Worksheets("Sheet1").Range("H6:H" & lnLastRow).Value
    End With
    
     'Retrieve the CClist of recipients.
    With wsSheet
      lnLastRow = Cells(Rows.Count, "H").End(xlUp).Row
      CopyToRecip = Worksheets("CC List").Range("H3:H" & lnLastRow).Value
    End With
    
Const EMBED_ATTACHMENT = 1454
Const EMBED_OBJECT = 1453
Const EMBED_OBJECTLINK = 1452

 On Error GoTo Run_Create_Draft_Note_Error

Set NotesSession = CreateObject("Notes.Notessession")

UserName = NotesSession.UserName
MyName = Right$((Left$(UserName, (InStr(1, UserName, "/") - 1))), (Len(Left$(UserName, (InStr(1, UserName,
"/") - 1))) - 3))

Set NotesDb = NotesSession.GetDataBase("", "")

Call NotesDb.OPENMAIL

Set NotesDoc = NotesDb.CreateDocument

Set richStyle = NotesSession.CreateRichTextStyle

Call NotesDoc.ReplaceItemValue("Subject", "--Add Subject Here--")

NotesDoc.SendTo = SendToRecip
NotesDoc.CopyTo = CopyToRecip
NotesDoc.BlindCopyTo = BlindCopyToRecip

Set NotesRTF = NotesDoc.CreateRichTextItem("Body")

Call NotesRTF.AddNewLine(2)

'Below embeds an attachment...
'MyAttachment = "MyExcelFile.xls"
'Call NotesRTF.EmbedObject(EMBED_ATTACHMENT, "", MyAttachment)

Call NotesRTF.AddNewLine(2)

Call NotesRTF.AppendText("Add Text to appear in the Email eg. Signature")
Call NotesRTF.AppendText("Please advise if you wish to be removed from the distribution list. ")

' Add a disclaimer section to email body.
Call NotesRTF.AddNewLine(2)
richStyle.FontSize = 10
richStyle.Italic = True
Call NotesRTF.AppendStyle(richStyle)
Call NotesRTF.AppendText("Add Disclaimer Text Here")
Call NotesRTF.AppendText("of the information contained on this E-mail.")
Call NotesRTF.AddNewLine(1)

'NotesDoc.PostedDate = Now()     'Remove ' to send each time.
NotesDoc.RemoveItem ("DeliveredDate")
NotesDoc.SaveMessageOnSend = True
Call NotesDoc.Save(True, False)

'Call NotesDoc.send(False)

Set NotesSession = Nothing

Run_Create_Draft_Note_Exit:
MsgBox ("The E-mail has been successfully created. Please check the Lotus Notes Drafts Folder for Final Review and
Submission."), vbInformation
Exit Sub

Run_Create_Draft_Note_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
Resume Run_Create_Draft_Note_Exit

End Sub


Hi guys,

I've been desperately looking for an answer on this one and I'm hoping someone can help.
I have created a macro that will copy and paste a range of cells from Excel into a new email in Lotus notes ready to send when I want it to.
My issue at the moment is that all of my emails should contain a signature in the footer: I believe that when I select 'new message' in Lotus notes it opens a new email with my premade signature at the bottom; a sort of template I guess.
My macro at the moment seems to paste over all original text in a new email which of course means that the Signature is removed.
I've tried so many ways to solve this but have yet to have any luck: the closest I have come is pasting another lot of cells that look like the original signature however the Hyperlinks then do not copy over.

If anybody can lend there expertise I would be most grateful!

Thanks in advance

Mr F

p.s. more than happy to paste my code if that makes it easier.