How To Create Outlook Email And Attach

Jun 10, 2014

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?

View 6 Replies


ADVERTISEMENT

Excel 2010 :: How To Convert Sheet To PDF And Attach To Outlook Email

Jan 30, 2012

I am looking for a macro that will convert the active sheet in an Excel 2010 file to a PDF, attach the PDF to an Outlook 2010 email message, and fill in the subject line and recipient address which are the same each time.

View 3 Replies View Related

Attach PDF To Outlook Using Macro

May 21, 2014

I have a sheet with an invoice on and wish the Macros to make it easy for users.

I have this code which saves as PDF, Prints and saves my worksheet. However I want to send the PDF to Outlook with To: and cc: completed using the Macro.

Sub PlasticParts()
'Saves active worksheet as pdf using concatenation
'of A1,A2,A3
Dim fName As String
With ActiveSheet
fName = .Range("A1").Value & .Range("F2").Value & .Range("A3").Value
.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"
[Code] ..........

View 1 Replies View Related

Convert Excel Sheet To PDF And Attach In Outlook And LOOP Using VBA?

Aug 7, 2014

Here is my code I got to work, however I have multiple excel files at a time.How do I not open the excel doc to run and add a loop to repeat the process for all files in the folder?

The MSG piece is critical and there is specific information needed in the body, can I add on to this?

CODE
------------------------------------------------------------------

Sub PDFEmail()

'Declare variables
Dim MyFilePath As String
Dim MyFileName As String[code]....

View 4 Replies View Related

Excel / VBA / Outlook Integration - Attach Most Recent File

Jun 21, 2013

I am trying to create a script for a manually morning process that I need to complete that entials downloading a number of .PDF files and sending them out to a distribution list. I am unable to automate that actual saving of the files onto the network drive, but I believe I can automate the process of attaching them to the email. I have run across some problems so far though, this is what I have been able to come up with. After doing a bit of research it seems as though using cmd would be the best way to locate and attach the file but I am still unable to get it to run properly:

Code:
Sub AttachMultiple()
With CreateObject("Outlook.Application").CreateItem(0)
.To = "test@gmail.com"
.Subject = "Test"
.Attachments.Add Split(CreateObject("wscript.shell").exec("cmd /c Dir 'G:Client ReportingMGIDaily
ReportsAGFAJune 2013*.pdf' /b /o-d").stdout.readall, vbCrLf)(0)
.Send
End With
End Sub

Here is an example of a folder I will be trying to pull the most recent saved PDF from, there are about 10 similar folders:

I feel like I am relatively close to completely this, my cmd syntax may just be a little off.

View 5 Replies View Related

Search Outlook Email Folder For Particular Subject Then Return Date Email Received

Nov 7, 2013

I would like to search an outlook folder for a variable in the subject line and return the date when that email was received. Here is the code i have so far...

Code:
Sub Get_pos()

Set olApp = CreateObject("Outlook.Application")

Dim olNs As Outlook.Namespace
Dim Fldr As Outlook.MAPIFolder
Dim olMail As Variant

Set olApp = New Outlook.Application

[Code] ........

View 2 Replies View Related

VBA Outlook Email Auto-send Routine - Check If There Any Attachments In Email To Be Sent

Jul 23, 2014

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.

View 2 Replies View Related

Attach Worksheet To Email

Jun 18, 2007

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

View 9 Replies View Related

Email From Excel - Can't Attach Worksheets

Dec 29, 2011

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

[Code].....

View 1 Replies View Related

How To Attach Current Excel Workbook To Email

Sep 19, 2013

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.

View 6 Replies View Related

Auto Email Attach And Send Via Lotus

Feb 10, 2009

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?

View 9 Replies View Related

Email Using VBA And Keeping Outlook Signature On The Email

Feb 5, 2009

I'm creating an excel file that can email itself inside the body of a HTML in Outlook. I have found some code from the web and have re-fitted it so that I may use it for my purpose. My main issue is this: I cannot keep my default Outlook signature on the email when it gets sent.

View 3 Replies View Related

Attach Files To Email Based On Partial Filename?

Jul 31, 2014

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

View 2 Replies View Related

App To Open User's Email Client, Attach File

Apr 8, 2008

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.

View 9 Replies View Related

Mail Macro - Attach Saved PDF File To Email

Nov 21, 2013

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've attached the script.

Sub Macro1()
'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:UserReportsPDF Reports" & Range("Q3").Value _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True

[Code] ........

View 8 Replies View Related

VBA - Attach Multiple Files To Email Using Folder Path Loop?

Feb 7, 2014

I have two pieces of code that each work, but I am struggling to combine the two.

I started with Ron de Bruin's code to attach multiple files to an email and then found more code that will loop through a folder to attach multiple files to an email.

Essentially, I would like the structure of Ron de Bruin's code, with the ability to have folder paths in the cell range, rather than file names.

Here is the code that works to loop through a folder, but it only works when I have the paths listed in one column, not in a range (e.g. columns C-Z)

Code:
Sub Send_Indv_Files()
Dim OutApp As Object
Dim OutMail As Object

[Code].....

View 4 Replies View Related

How To Outlook Email In HTML

Jun 26, 2014

Below I have my code that works great. What I would like to do is make it pull the .HTMLBody from a cell value instead of inbeded in the code so users can change, just like to I have it below for who I am sending it to. The issue is if I do it the same way I loose my signature and the font, size and color I have below. How can I do this.

range("P16") would be the 1st line of the mesage
range("P17") would be the 2nd line of the mesage
range("P18") would be the 3rd line of the mesage

All the sendkeys below copy a range and paste it below my message as a picture currently. That is fine.

[Code] .....

View 1 Replies View Related

Outlook Email Warning

Oct 12, 2009

I have some code to literally email a workbook to a specific user. But when the email is sending...i am getting the warning form Outlook whethere to send or not. If the user clicks No...then this opens up a can of worms when asking to debug. Is there a way i which i can amend the code to disable this warning and send regardless...without changing security settings in outlook???

View 2 Replies View Related

Outlook VBA For Email Filter?

Jul 25, 2014

I need a VBA code with the below requirements.

All my emails have a specific Subject line pattern. e.g. if the request is for Jason.. The subject line is "Issue with Desktop [JAS].. There are 10-15 such names and each one of them has a short name in the subject line of the email.. For each of those names there is a folder. I would want subject lines with [JAS] to move to JAS folder and so on..

View 2 Replies View Related

Email Range Via Outlook

Feb 15, 2010

I am using a code copied from Ozgrid to email a worksheet but the code doesn't seem to work?

Sub Mail_Sheet_Outlook_Body()
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
With Application
.EnableEvents = False
. ScreenUpdating = False
End With

Set rng = Nothing
Set rng = ActiveWorkbook.ActiveSheet.PrintRange 'UsedRange
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon .......................

View 8 Replies View Related

Email A Range Via Outlook

Jun 13, 2007

I have a range in an Excel worksheet named 'Request'. I want to paste this range into the body of an email and send to sepcified recipient via outlook. I tried the following code, but this does not seem to work. Am I not referencing to Outlook correctly?

Private Sub cmd_Send_Request_Click()
Dim objol As New Outlook.Application
Dim objmail As MailItem
Dim varBody As String
Dim objdata As DataObject
Set objol = New Outlook.Application
Set objmail = objol.CreateItem(olMailItem)
Set objdata = New DataObject
Application.Goto Reference:="Request"
Selection.Copy
objdata.GetFromClipboard
varBody = objdata.GetText
With objmail
.To = "kully.rai@ca.com"
.Subject = "Benchmarking Request"
.body = varBody & vbCrLf & vbCrLf
.NoAging = True
.Display
End With
Set objmail = Nothing
Set objol = Nothing
End Sub

View 4 Replies View Related

Email One Sheet In Workbook Through Outlook?

Aug 29, 2012

I have attached a version of my file with code that I have adjusted. Everything is working except for the save as portion of it. I keep getting a run time error 1004. The save as of the workbook failed. Also, when I run the macro in the template that I have uploaded it opens up the original file that I had the code in.

View 2 Replies View Related

Excel 2010 :: How To Set To Email From Outlook

Jan 30, 2014

I run excel 2010 on a windows 8.1 machine. In excel I have email address that I need to mail from however when I click the hyperlink it automatically directs me to Hotmail. Is there a way to change that to automatically load Outlook instead?

View 7 Replies View Related

Compose Outlook Email Without Sending

Jun 17, 2014

I am trying to write a macro to compose an email in outlook without sending it. Ideally the macro would compose the email and the email would be left open for me to inspect before sending. Below are the details.

Column A contains the addresses
Column B contains the CC addresses
Cell C2 contains the message

I can change where the above info is located/arranged if need be.

My spreadsheet automatically generates the email addresses, which are dynamic. For control purposes my employer does not want me to have the email automatically sent, being the reason for me to want to inspect the email before sending it.

View 2 Replies View Related

Sending Email From Excel Via Web Outlook (OWA)

Jun 23, 2014

i have the following code in my Excel worksheet. This code successfully send out email to our customers when if the criteria is true. I have tested this at home and it works perfectly as I use Outlook at home. However when I take this code to work I couldn't get this working as at work we use Outlook 365 and we use web Outlook, OWA.

Is there a setting I can specify to use OWA as I don't think I am allowed to install outlook at work.

[Code] .....

View 5 Replies View Related

Send Automatic Email To Outlook

Oct 30, 2008

I have a spreadsheet with a number of different actions with various different dates associated to them. Is it possible to create a macro that will automatically send an email to my outlook inbox (or somebody elses) when the date is due or upcoming?

Also is it possible to include different content in the email based on say the title of the action etc?

View 9 Replies View Related

Pulling Email Information From Outlook?

Apr 12, 2012

Is it possible to pull the subject and from information from the folder "ESITS" in my Outlook email into an excel spread sheet?

View 1 Replies View Related

Sending Personalized Email Via Outlook

Dec 9, 2012

Determining the problem with my settings for the failure of this line of code executing.

Set OutlookApp = New Outlook.Application

I was expecting the statement to create a new outlook instance, but got this message

Run-time error '-2147319779 (8002801d)':

Automation error Library not registered.

View 9 Replies View Related

Importing Email Addresses Into MS Outlook

Feb 9, 2013

I have to find a way to transfer a total of 2,970 email addresses into an Outlook single email Group...

View 1 Replies View Related

How To Send Email To From Excel Using Outlook

Apr 20, 2013

I am trying to make a macros for sending an email through excel using outlook.

The body of the email should contain ..(The following case has been processed and ready for Quality Check and also picks the case# from Cell "C3" and "D3")

The subject line should say "Case Processed and take the case # from Cell "B2".

It should also show the "To" Field and "CC" too.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved