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 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 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
I have a macro to send emails to a group people that based on various criteria, it will attach a number of files to a recipient's particular email. This is a small portion of the code that does the attachment adding:
Code: If Range("B" & a) = "Y" Then If citChev "" Then .Attachments.Add citChev End If If Range("C" & a) = "Y" Then If citMits "" Then .Attachments.Add citMits End If If Range("D" & a) = "Y" Then If citToyo "" Then .Attachments.Add citToyo End If If Range("E" & a) = "Y" Then
[code].....
Most people get several of the files attached to their email and all works fine. But, there are certain conditions when all the IF() stmts fail where a recipient will not get any files attached. I do not want to send the email if this is the case, but it currently is sending it.
Is there a way after all the IF() stmts have processed to check to see if this current email has any attachments assigned to it? (IF .Attachments "" Then...) does not work.
I need to send an email reminder to teachers on the day they have duty. I know how to set up an email that sends on a certain date, but I am unsure how to code it when the date changes. I have attached the spreadsheet.
In cell A 53, I have the date November 24th. In cell C53, I have the email address of the teacher doing duty that day. In cell F53, I have the date November 25th. In cell, I53 I have the email address of the teacher performing duty that day. On November 24th, the person in C53 should receive a reminder email. On November 25th, the person in cell I53 should receive a reminder email. I will have spreadsheet for every month of the year. So there will be LOTS of changing dates. How do I code this when the date keeps changing?
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
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 been trying to find a method to attach a worksheet to an email WITHOUT sending it. I want my user to be able to create the attachment file and then add some text before sending.
also, I have been playing around with Ron Debruin's code to send one worksheet, found at [url] this is the closest i have found to what I need. except it sends the mail for you. and I can't get it to copy and paste as values only one cell on the worksheet (not the entire worksheet as values).
here is his code Sub Mail_ActiveSheet() '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 Dim AddresseeName As String
I have specified the email addresses in the code to whom the email will be sent when the user presses "Send Email" button but now I want to add all the addresses in the Access table and write down the code that will send an email to those people whose addresses are in the Access table.
I have this Macro working almost perfectly, but I need to attach the worksheets to the emails. The code is below. I am unable to reference the ".Attachments" to the workbook created previously in the code.
Sub ActivityReport() ' Clear out any old data on Report Sheets("Report").Select
I'm generating a letter (Word doc) and populating bookmarks with data on a worksheet using the following code which is assigned to a Button and it works a dream (in terms of opening the Doc and populating the bookmarks with whatever data is in the relevant cells on the worksheet):
Sub test() Dim objWord As Object Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") Set objWord = CreateObject("Word.Application") objWord.Visible = True objWord.Documents.Open "location of the letter.doc"
[code]......
However, what I now need to do is Create an Outlook Item (email) with a Standard Subject and send to an email address that will be on the same worksheet (in cell M2 for instance) and with a standard Body.
I dont really need to open the Word doc, like it does at present, but I do need the Bookmarks contained within it to be populated with the source data contained within the Worksheet and subsequently have it attached to the email fully populated - maybe I need to 'close' / 'save as & close' the Doc post generation?
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 .........
show me the visual basic codes that allow me to attach the current saved workbook to an email (just attach, but not to send the file). So basically, just open the Outlook, send to, and attach the file.
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 have yet to find ANY information online relating specifically to emailing any given range in Excel via Lotus Notes. The codes that do exist out there either fail to fully copy the range or relate to attaching an excel file into a Lotus Notes Memo.
Below I have copied the code the I am working with, but I keep getting the following error: Error # 438, Object doesn't support this property or method. After making some minor tweaks, it might then give me another error, Error 424 Object Required, but the range then doesn't copy into the Lotus Notes memo at that point.
I have highlighted the area that I think the code is failing at, that being the actual act of copying/pasting the range selected into the Lotus Notes Memo. With the current code, the selected range does get fully copied into a new memo in Lotus notes, but these errors populate in Excel.
Can anyone offer any suggestions to resolve this issue? I have spent hours on this and I'm sure there are many others out there that might be able to benefit from a solution as well.
I'm currently creating E-mails containing a table of material numbers in the body of the message. The E-mail is generated with a macro from a table in excel. In some folder - say on the desktop - I have PDF files for each material Number. Each file is called 0012345_Product-Name.pdf where 0012345 is the material number (always 7 digits). I want the macro to look for the pdf for each material number found in the table and add it to the E-mail as an attachment. With constant, known file names this wouldn't be much of an issue. However, I'm having trouble getting the macro to only search for the first 7 digits of the filename and select it based on that.
Since we are talking up to several 100 PDF's, it would also be great to zip them, once selected - if at all possible, before attaching them to the E-mail
I have developed a product that's a bunch of Excel spreadsheets strung together. Real estate and mortgage professionals use the tools to counsel home buyers. We include a pdf converter in case recipients of emailed file attachments don't have Excel.
We provide an 'Export' procedure that calls a custom dll that uses the outgoing mail server that the user [must] setup or our web's mail server as a default. The action attaches the Excel or the PDF file and sends in the background. We use an Excel form for user to write the message and the process saves a copy of the message sent and the file send in an Archives folder.
This procedure is not very user friendly, as it does not offer the user's address book to select a recipient, does not put the message in the user's email client Sent folder...Plus the Archives folder could become quite large...plus it involves a user setup that creates alot of support calls.
But actually the most critical shortcoming has become that ISPs (Comcast and ATT so far) are blocking the use of custom email clients!
Recently my son showed me in QuickBooks their attach file-to-email process. It simply opens the user's default email client with the file attached. I need an app that does that! And it needs to be distributable.
I have written a macro but unable to make it work. The macro works but not the way I would like it to.
The macro has to save an excel sheet to a separate folder as a pdf format, a message box should pop up confirming the pdf file and then attach the same saved pdf file to an email.
Now the macro saves the pdf file where I need it to be saved, the msg box pop up and then the pdf file opens up on top of the msg box. If I close the pdf file the msg box which was hidden under the file is now shown. When I click "OK" it goes straight to outlook with the email addresses attached but the saved pdf file is not attached.
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 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 wanting to send an automatic email notification to several lotus notes accounts once a specific cell in my excel spreadsheet has been entered in (It does not matter what is entered into the cell).
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...........
