I have Excel 2003 and Lotus Notes Version 6 and I need to send out the active workbook from Excel. I have searched through the existing threads, but all the code that I run results in errors -
1. I dont want this to auto send. I want it to compose the mail and leave it ready to hit the send button in lotus notes. How do I do that?
2. Would like to include multiple emails. If I put two emails (separated by comma or semicolon into cell C19, Lotus notes sends as one address and it bounces. (This is not terribly important, but would be a nice feature)
3. Would like to insert the signature line that's already configured on lotus notes. Since this auto sends the email, the signature is not added. Any idea how to do this?
4. Now this would be really nice, tell the macro which database to use. The one I want to use is not "my" email, but a shared email. It lives on server "Notes1/recovery" , in folder "mail" with a database named "company.nsf". Is there a way to do this?
Here is my code that works, but needs the above features.
Sub SendNotesMail() Dim Maildb As Object Dim UserName As String Dim MailDbName As String Dim MailDoc As Object Dim Session As Object Dim Recipient As String Dim Subject1 As String Dim ccRecipient As String
putting a macro button on an individual worksheet so the user can click a big easy button and a new email will pop up in Lotus notes with the worksheet attached for them to input the address and then send the worksheet..
I prefer to:
Send individual worksheets only
The ability to input the email address is the new composition email in LOTUS Notes
Am trying for a code which will send mail to recipients mentioned in column A [to] Coulmn B [CC] & formatted range should be pasted as body of email lets say C:F. Each time formatted header should remain same is it will contain column heading. I have a code which sends mail to specified recipients mentioned in code .
Code:
Sub Send_Formatted_Range_Data() Dim oWorkSpace As Object, oUIDoc As Object Dim rnBody As Range Dim lnRetVal As Long Dim WB6 As Workbook Set WB6 = Application.Workbooks.Open("C:TESTDaily_BKG.XLS") Const stTo As String =
I have a macro that sends a custom email through lotus notes. It works, but will only generate 1 email instead of looping through my list. If I have "next r" at end I get email of 1st record. If I put "next r" before "On Error GoTo Audi"
I get last recorded emailed but not 1st 2. I don't know how to get it to send all. It seems the for next loop is jacked up.
For you to see macro work you need to have lotus notes and put email address in column B.
I am using a version of the following code that sends a mail using Lotus notes and attaches the Workbook - I am wondering if there is anyway I can make it attach the WORKSHEET instead ?
Code: 'The procedure for executing the main task: 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 As Variant, vaMsg As Variant
I have an excel file that will be downloaded by multiple users and saved locally. I was wondering if there was a way to build code into the file so that everytime it is saved an email would be created in lotus notes from the user to me with a copy of the file attached. Is this possible?
I have developed many macros that generate automatic emails from excel using Outlook, with the excellent help I've found here. Now due to recent buyouts, we have changed our email from Outlook to Notes. I'm looking for the simplest method of changing my macros so they will work with Notes. Below is the typical code I'm currently using. Is there an easy way to change it for Notes?
Public Sub SendEmail()
Dim OutApp As Object Dim OutMail As Object Dim Dist As String Dim supplierinfo As Range
I am looking to build a macro that can automatically email a recipient upon their name being selected from a drop down list. It designed to be for a warehouse, so when new packages come in we can scan them in and have excel send an email out once our receiving team determines who it is for. With their being multiple recipients, I would like the code to have an IF type function that choosing the correct recipient. the Column titles "Recipient/Dept" is where the dropdown list is located.
It would be ideal for the code to just utilize an open Lotus Notes Session and use that to send the email.
Attached is the document with what I have thus far : Warehouse barcode test.xlsm
I have found the code that I need to send one spreadsheet or the entire workbook via lotus notes through VBA, but I cannot seem to find the way to email 2 specific spreadsheets anywhere..
I have tried both of these, but I get an error when running:
Code: With Worksheets("Diario") And Worksheets("Periodo") .Copy stFileName = Worksheets("BD").Range("A1").Value & "- NDG " & Worksheets("Code").Range("K22").Value Debug.Print stFileName End With
Code: With Sheets(Array("Diario", "Periodo")) .Select Selection.Copy stFileName = Worksheets("BD").Range("A1").Value & " - NDG " & Worksheets("Code").Range("K19").Value Debug.Print stFileName End With
I would like the 2 sheets (Diario & Periodo) to be copied both to the same temporary workbook...
Any ways to automatically send emails using Lotus Notes.
On Lotus Notes, I automatically have my signature to append to an email, however when I am running Johns script at the bottom it is replaced with the file path of my signature (i.e. C:Program Fileslotus otesdatasig.htm), and when I comment out the part where strSignature is mentioned there is no signature.
My code is below:
Sub mySub() Dim x As Integer Dim UserName As String Dim MailDbName As String Dim Recipient As Variant Dim Maildb As Object Dim MailDoc As Object
I managed to create the attached macro with help from stuff I've found in the internet. With this code, I am able to save the file and then send an email cia Lotus Notes. I need two things:
(1) The code to attach the file saved into the email that it sends
(2) The code so that the file is protected so that changes are not made by the recipient.
HERE IS THE ActiveSheet.Copy ActiveWorkbook.SaveAs filename:="V:TFMCO8 ReportingHistory" & [c22].Value & " " & Format$([c20], "- (YYYY-MM-DD)") & ".xls" ActiveWorkbook.Close False
Dim Maildb As Object, UserName As String, MailDbName As String Dim MailDoc As Object, Session As Object
Set Session = CreateObject("Notes.NotesSession") UserName = Session.UserName MailDbName = Left$(UserName, 1) & Right$(UserName, _ (Len(UserName) - InStr(1, UserName, " "))) & ".nsf" Set Maildb = Session.GetDatabase("", MailDbName) If Maildb.IsOpen Then Else: Maildb.OpenMail...........
Finally I have the code all okay for Sending Reports, and other extra comments etc...
And now... Is there code available for picking up images ( Screen snaps) and inserting into an email in Notes after the Body of the Message?
Either a reference to a link or some code would be greatly appreciated, cos I cannot find any info at this point in the archives ( Presuming I have searched using proper parameters ).
I have searched high and low on the forum and can not seem to find a solution to my problem. Here is my problem (besides being new to Lotus Notes): I need a button on my excel worksheet that gets information from Column H in the attached spreadsheet and puts all the emails in the To: (could be >than 100 email addresses) Field in Lotus Notes. I would like to have the subject read "New Email from Your Basin Coordinator" and have the CC list come from a second worksheet (CC).
I have an Excel sheet that I use as a mailing automatism for reports. As it currently is it attaches an actual copy of the excel workbook to the email and send them out. The mailer contains several different people, and they get different report each day. Due to the size of some of the files, I am starting to run into an issue where I cannot sent the emails anymore because they are too big, so I am wanting to switch to sending links to the files instead, and I have hit a wall.
I use Lotus Notes 8.5. The VBA will cycle through a range, and each cell has a list of report delimited by a ",". It takes the list and passes it to the mailer as a string. The mailer takes the string, turns it into an array and splits it out, and then checks to make sure the reports are current. One email could have up to 10 different reports in it. I have tried creating an HTML MIME email to include the links.
Here is the code I currently have:
Code: Sub Send_HTML_Email(ByRef Name As String, ByRef Address As String, ByRef Reports As String) Const ENC_IDENTITY_8BIT = 1729 'Send Lotus Notes email containing links to files on local computer Dim NSession As Object 'NotesSession Dim NDatabase As Object 'NotesDatabase Dim NStream As Object 'NotesStream Dim NDoc As Object 'NotesDocument
I am trying to send an automated email by use of a "email" button. What I want it to do is to pop up a input box that will ask me who I want to send the email to, and once I hit ok it will send open up outlook and send the email. I have the code to work if I want it to be sent to a specific email address, but I can't seem to get the email address entry part to work. I will attach my code as it lays right now.
Private Sub CommandButton2_Click()'Need to reference: Microsoft Forms 2.0 Object LibrarySet OutApp = CreateObject("Outlook.Application")OutApp.Session.LogonSet OutMail = OutApp.CreateItem(0)strbody = "This is the most up to date copy of EAS Tracking 2.0 as well as the Resource Planning Sheet."attachmnt2 = "C:My DocumentsResource Planning Sheet_External.xls"On Error Resume Next'?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|? BELOW IS WHERE I CAN'T GET TO WORK!!!With OutMailDim range As Longrange = Application.InputBox("How many copies do you want?", "Number of Copies").To = range.Subject = "This Weeks Reports".Body = strbody.Attachments.Add (attachmnt2).Display.SaveEnd With'__________________________________________________attachmnt3 = "C:My DocumentsReport DataWork Request Tracking Data FolderEAS Request 2.0.xls"On Error Resume NextWith OutMail.Subject = "This Weeks Reports".Body = strbody.Attachments.Add (attachmnt3).Display.SaveEnd .........
I have an excel sheet where automatically live data comes in.
When the cells reach a certain value the value in cell R1 becomes 1.
I want to make a macro in vba that sends me ONE email if the value becomes 1.
I tried :
Public Function MailS(mailtrigger As Double) As Double If mailtrigger = 1 Then Call makeandsendemail Application.Run "makeandsendemail" MailS = 1 Else MailS = 1 End If End Function
I wrote this code years ago to Auto Send Emails from Outlook...
Sub Emailfile()
Dim OutlookApp As Object Const olMailItem = 0 Set OutlookApp = CreateObject("Outlook.Application") Dim EmailContent As String With OutlookApp.CreateItem(olMailItem) EmailContent = Range("A1").Value .To = "Test" .Subject = "LSG Data File" .Body = EmailContent .Attachments.Add ("P:MI TeamICCE LEADS EXTRACTSLSGLSG Data File")
End With
End Sub
I can't figure out how to get it working for Lotus notes... So 2 things I guess really... Firstly is it possible? Second: If it is, how can I get it to send say 16 emails each with a specific attachment but the same commentary?
I used Scheduled Task to set up my spreadsheet to open daily. I have the code with assistance to pull out the due date items and place them into an email.
I have come across XLSTART/AUTOEXEC/ACTIVATE...ETC... But cannot figure out the code that will automatically "enabling macro" once Scheduled Task opens the spreadsheet?
Then once the macro runs, the email with the due dates, how can this auto send without user interaction? (currently I would have to hit send)
I am trying to make the process totally automated to open the spreadsheet at a certain time, send the email with due dates and close the spreadsheet.
Following code in ThisWorkbook--
Private Sub Workbook_Open() Check_Date_Send_Mail End Sub Code in Module1--
Option Explicit Sub Check_Date_Send_Mail() Dim wbBook As Workbook Dim wsSheet As Worksheet Dim rnDate As Range, rnValue As Range Dim stAddress As String, stMsg As String Dim stRecipient As String, stSubject As String Dim stPost As String Set wbBook = ThisWorkbook Set wsSheet = wbBook.Worksheets("Sheet1") With wsSheet Set rnDate = .Range("d2:t23") End With
I have problems with passwords in excel 97. The perfect Script below wheel in excel 2000 and 2003, but presents problems in excel 97.
Function EnviaBancoALTEND(Para As String, Doc As NotesDocument, DB As NotesDatabase, Password As String)
Dim ws As New NotesUIWorkspace Dim s As New NotesSession Dim Doc_Mail As NotesDocument Dim RTItem As NotesRichTextItem Dim object As NotesEmbeddedObject
Dim xlApp As Variant Dim xlsheet As Variant
'cria o excel Set xlApp = CreateObject("Excel.application") xlApp.Visible = False xlApp.Workbooks.Add Set xlsheet = xlApp.Workbooks(1).Worksheets(1) XlApp.Windows(1).DisplayGridlines=False
I am developing a VBA program that will take rows of data from a worksheet and create Lotus Notes Calendar entries.
My code works fine until it wants to add a calendar entry dated in the past... at this time I get a pop up message on Notes saying "This entry is being created in the past.
Create anyway?" and an option to click yes or no..
how to either suppress the pop up or programatically supply a "YES".
I'm in the middle of creating an auto-mailer via Excel VBA (and got most of it working) but I've run across a small(ish) snag.
To save me about 3 hours work, I need to get the subject of the stationery, the "To" list, the "CC" list and the "BCC" list for every stationery (currently around 150).
Is there any way of getting VBA to read all the stationeries on our inbox (it's shared) and giving me the info I need?
I'was given a piece of code to read through stationeries in Lotues Notes so I could quickly retrieve all the Sendto, CC and BCC information.
I'm now trying to modify that so I can check two specific folders in a mailbox and retrieve all the dates that e-mails have been received on. Code is here:-
Sub new_reader() Dim Maildb As Object, view As Object, Session As Object, entry As Object, entries As Object
Sheets("Sheet4").Select counter = 2
Set Session = CreateObject("Notes.NotesSession")
Set Maildb = Session.GETDATABASE("servername", "boxname") If Maildb.IsOpen = False Then Maildb.OPENMAIL End If
I received an excel spreadsheet through lotus notes e-mail. I opened it from the e-mail and worked several hours on it, sporadically hitting "save" along the way. However, I do not recall having ever done "Save As" and specifying a location to save it in. I later closed excel. Now I cannot locate the spreadsheet (I have searched). Do you know where it might be located or how I might recover it? When you open spreadsheets directly from e-mails and just hit save (not save As),
I have a networked spreadsheet that users access, process data, and then e-mail the results to a common recipient.
Until recently we have all been on Lotus Notes - and my e-mail VBA worked OK.
However, some users are now using Outlook (the new Corporate standard) while some remain on Lotus Notes. The Lotus Notes users now say the e-mail part of the process no longer works for them.
Can VBA detect whether users are on Outlook or on Lotus Notes?
Should I give them a prompt - "Which e-mail do you use? Enter L for Lotus Notes or O for Outlook" - and if they answer L then use specific Lotus Notes VBA for the e-mail process or if they answer O use Outlook-specific VBA?
The code is this: Dim finder As Object Private Sub ComboBox1_Change() Set finder = Sheets("Sheet1").Columns("A:A").Find(what:=ComboBox1.Text, lookat:=xlWhole) Main_recipients.Value = finder.Offset(0, 1) End Sub Private Sub Send_email_Click() With Application .ScreenUpdating = False .DisplayAlerts = False End With
Now, everything works fine until it gets to this line:- MailDoc.SEND 0, Recipients
when I get an error message that states "Unable to send mail, no match found in Name & Address Book(s)"
In Lotus, I created a list of recipients (including any additions after the name) and copied that into the cell that feeds the text-box "Main_recipients".
The odd thing is, if I just email myself without any additions after my name it's fine. It's when I try to e-mail multiple people (with and without additions) that it falls over.
