Solved

Lotus notes Send Email

Posted on 2010-11-17
7
490 Views
Last Modified: 2013-12-18
Hi,

I have attached a spreadsheet.

I have code to send an email from lotus notes in Module two.

I would like the email to be automatically sent when either of two events happens

(1) IF N68 (CA Checklist) = "Send Email" at 12.00, then Send Email
(2) IF N69 (CA Checklist) = "Send Email" at 18.00, then Send Email

I put some code in Sheet1 (CA Checklist) for the recipitant and subject.

The password for the project is "protectprocess"

Any suggestions would be great!

Thanks
Seamus
CA-Checlistxlsv1.zip
0
Comment
Question by:Seamus2626
  • 3
  • 3
7 Comments
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 34156711
For how to send mails from Excel, see http://www.fabalou.com/VBandVBA/lotusnotesmail.asp or search Google using
      excel vba send mail "lotus notes"
to get a shipload of suggestions.
0
 

Author Comment

by:Seamus2626
ID: 34157297

I have tried to code myself

If you look at the two new subs in  CA Checklist and then Module 3.

Everytime i try to run these nothing happens?

protectprocess is password

Thanks
Seamus
CA-Checlistxlsv1.zip
0
 
LVL 22

Expert Comment

by:Bill-Hanson
ID: 34157344
As far as I know, you can't schedule code to run in Excel, so sending email at specific times from within the spreadsheet probably won't work.  Most events occur when someone changes something.  For this to work, you just add a function named Worksheet_Change to your worksheet code.  The [Target] parameter tells you what was changed.

    Private Sub Worksheet_Change(ByVal Target As Range)

If you need to send email at specific times, you'll need to use a scheduler to trigger your code, but I don't think you can do this from VBA within the spreadsheet since you'd have to open the spreadsheet in order to trigger the macro.  My suggestion is to switch to a compiled executable which uses COM to do the same thing.  Then call the exe from your scheduler.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Seamus2626
ID: 34157473

Hi Bill,

I was thinking the same regarding the time. The worksheet would need to be constantly refreshed.

What is a compiled executable which uses COM?

Thanks
Seamus
0
 
LVL 22

Expert Comment

by:Bill-Hanson
ID: 34157478
I looked at your code, and the main problem is where you call Mail_Sheet_Outlook_Body.  When I run the code, I get an exception with this message 'Argument not optional' on that line.

You have Mail_Sheet_Outlook_Body defined in Module3 as...

   Sub Mail_Sheet_Outlook_Body(Total As String, P1 As String, P2 As String, Spec As String, P4 As String, P5 As String, Student As String)

... which means that you need to pass in all parameters when you call it.

Also, you are not comparing times correctly.  Here's how I would write this part of the Worksheet_Change function...
Private Sub Worksheet_Change(ByVal Target As Range)

 If Time < CDate("12:00 PM") Then Exit Sub

 If Intersect(Target, Range("n68")) Is Nothing Then Exit Sub

 If LCase(Target) = "Send Email" Then Call Mail_Sheet_Outlook_Body("Total", "P1", "P2", "Spec", "P4", "P5", "Student")

End Sub

Open in new window

0
 
LVL 22

Accepted Solution

by:
Bill-Hanson earned 500 total points
ID: 34157582
RE: "What is a compiled executable which uses COM?"

MS Office exposes the entire VBA API as a COM interface.  This is just fancy talk that means you can code VBA from just about any Windows development environment (like Visual Studio .Net).

Since you're used to VBA, I would create a Visual Basic project and add a COM reference to the "MS Office XX Object Library" where XX is the MS Office API version you have installed on your machine.  Then in your code, you just use the reference to control the application.  This is how most applications interact with other programs under Windows.
0
 

Author Closing Comment

by:Seamus2626
ID: 34157631
Thanks Bill,

I think i will leave this task off as it is a little over my head at the moment.

im more advanced excel than vba!!

Thanks Again
Seamus
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now