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 RepliesI 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].....
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 RelatedI 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'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 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 have the need to email the current worksheet in Excel 2007, which I have been able to do with the following ...
Sub SendTab()
'Declare and initialize your variables, and turn off screen updating.
Dim wks As Worksheet
Application.ScreenUpdating = False
Set wks = ActiveSheet
[Code] .......
Is there a way I can also get it to also rename the sheet from the default "Book1" to the information in a referenced cell.
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'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] ........
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].....
I have a macro that will send an excel workbook by email when an image is 'Clicked on' however, I need the attachment to be renamed as the text used in cell B9 of this worksheet.
So far I have the macro below which auto populates the 'email to' address and the 'subject' line which is great but how do I get to rename the attachment?
Sub SendIt()
Application.Dialogs(xlDialogSendMail).Show
arg1:="test@test.com", _ arg2:="test 1" End Sub
I have a format and i want to put information in the format and send email only format not all workbook . i use also outlook for Email
View 9 Replies View RelatedI want my macro to be able to mail the active workbok through outlook using the email addressses in Cell R1 and having the body of the email have the text in cell R2:R8?
Dim OutApp As Object
Dim OutMail As Object
Dim ws As Worksheet
Dim range as range
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = ws.Range("R1:R8").Value
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = ws.Range("R2:R8").Value
.Send 'or use .Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Im trying to copy multiple workbooks and just save it into only one worksheet. I have 2000 diffrent workbooks with the diffrent amount of rows, The ammount of cells is the same and it dosent change.
im working on a excel 2010
This is what i got for the moment..
Sub LoopThroughDirectory()
Dim MyFile As String[code].....
I have multiple buyers that use an Excel workbook containing several macros to perform edits on other workbooks they receive via email. Generally, they have the workbook containing the macros open, and they can double-click the workbooks they receive in the email messages to open them, and the macros are available from the "master" workbook.
Recently, however, some of the buyers have been getting new computers running Windows 8 and Office 10. Now when they have the "master" workbook open, and double-click the attached workbook in an email, (Outlook 10), the new workbook opens in a new instance of Excel, and as a result the macros are not available to run on the newly opened workbook.
The work around has been to right-click the attachment and save it to their desktop, and then use the File/Open command from the "master" to open the new file. This is a hassle and takes extra time.
I'm sure I am overlooking a setting somewhere that tells Excel to always open new workbooks using the same instance of Excel, (there are two people using the new systems that aren't having problems, and two that are), but for the life of me I can't find where to set these parameters.
I did this 10 years ago so I admit I am rusty at this but here are what I had in my notes that worked with Excel XP. I am currently using Excel 2010
With ThisWorkbook
.HasRoutingSlip = True
With .RoutingSlip
[Code]....
The last line will send the workbook but I need a Subject and Message to go with it.
I am using 2007. This may be something simple but I can't seem to find a solution to this, I have an excel macro enabled workbook which produces a daily report the final task is to save and send an email but I need the file to save as an xls rather than xlsx as some of the recipients are unable to open the file here is the code used for the tasks in the
workbook:Sub Step1_Refresh()
'
' Step1_Refresh Macro
'
'
ActiveWorkbook.RefreshAll
End Sub
Sub step2_save_close()
[Code] ........
I am using 2007, I have an excel macro enabled workbook which produces a daily report the final task is to save and send an email but I need the file to save as an xls rather than xlsx as some of the recipients are unable to open the file.
Here is the code used for the tasks in the:
workbook:Sub Step1_Refresh()
'
' Step1_Refresh Macro
'
'
[Code]....
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
[code]...
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]....
I would like to send a Excel printing area to a mail recipient by using a macro that:
2) Save the printing area as a pdf - file - use a temporary filename
1) Opens MS Outlook mail
3) Attach the temp-file to the mail
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.
I have a spreadsheet that I send emails from. Currently I use the .PrintOut command just before .send and it prints the email to the default printer. Instead of printing to the printer i want to print the email to pdf or any file for archive purposes. I would also like the file to be located on a network drive with a file name from a cell in the spreadsheet with todays date. Is this possible?
Currently the code looks like this:
VB:
With OutMail
.to = sh.Range("A1").Value [code]....
I have a spreadsheet that I send emails from. Currently I use the .PrintOut command just before .send and it prints the email to the default printer. Instead of printing to the printer i want to print the email to pdf or any file for archive purposes. I would also like the file to be located on a network drive with a file name from a cell in the spreadsheet with todays date. Is this possible?
With OutMail
.to = sh.Range("A1").Value
.CC = ""
.BCC = ""
.Subject = sh.Range("H1").Value & " " & sarBody
.HTMLBody = strbody & "<br><br>" & "<H3><B>Specific Notes, If any...</B></H3>" & vbCrLf & varBody & Signature
.PrintOut
.send
End With
I am using Excel 2010. I have been given a task at work that can save my team a lot of time if I can solve the problem. Every month, we have a spreadsheet with about 5000 rows that we have to email. In each row, there is a range that we have to email to a specific email. For example, I would have to copy and paste Range A2-R2 in the body of the email, and then email it to whatever email is in cell S2. I would then continue this for the next 5000 lines, making it a possibility that i will be sending 5000 emails manually.
I have been trying to come up with a solution through VBA that would automatically send these emails. My goal is to automatically send the Range A2-R2 to outlook email, then cell S2 into the "To" email address box, and then automatically send it. So far, i have successfully been able to send one row, but cannot figure out how to loop it for the remainder of the rows.
A couple other key points are that I have column headings as well (Range A1-R1). If possible, I want to be able to include the column headings in the email body as well. Example - first email would be range A1-R2. second email would be range A1-R1 and A3-R3, and so forth. The body of the email would also contain a standard script, such as "Please review the information below."
The goal here is to save everyone from having to send 5000+ manual emails. This would be a big boost for my team.
Code:
Sub Email()
Dim rng As Range
Dim OutApp As Object
[Code].....
We know that professional emails have a fixed format. I have a list of people with their names and emails and there are some people for which emails are missing. I am pasting my sample data below. But first let me explain the meaning of columns.
Column A: Means full name of the person for which we have email id
Column B & C: just split of Available full name into first name and last name
Column D: Email id of the person with full name in column A
Column E: For the names in this column, we need to get email ids
Column H: Sample result
Now basis on the Full Name(Column A) and Email Id(Column D) can we get the email id for person in Column E.
Sheet4
A
B
C
D
E
F
G
H
1
Available Full Name
Available FirstName
Available LastName
Email for available full name
Actual Full name
Actual firstname
Actual lastname
Sample result
[Code] .........
I have an open workbook (A) and this is where the code should reside. I want to use VBA to copy the content of an entire worksheet from a closed workbook (B) to an existing worksheet in workbook A. How would you accomplish this?
View 7 Replies View RelatedI have 2 workbooks. 1 is where data is entered called wksPB, the other is like a database workbook. The wksPB has data to be feed from column B to F and there is a dropdown.
what im looking to do is, if the combobox value is selected as Decline then it should display a messagebox that reference value in cell B is denied. if combobox value is Agreed then the macro should search the database workbook for the text entered in column b of wksPB and then copy data of C:E of wksPB to H:J of database sheet where that text is found and also the approver name in wksPB C24 to the approver cell of where that text was found. I've reached till finding the text but what I get my head twiting on is how to copy the text from wksPB column B:F to database sheet column H:K. Im attaching the sample workbooks and the code where ive reached till.
[Code] .....
Attached Files
Replacement Records - 2014.xlsx
forum file.xlsm
I am using the below code (i have combined different bits of code which i found on these forums) to open a new workbook, copy all the data in it and paste it into Sheet2 in the active workbook . Also, i want the data to be pasted on the next empty row as there is already data in Sheet2. The data that is being copied and pasted has the same format and it is going to be repeated many times.
The problem i am having is that it is not offsetting the data to the next empty row - i think it is a simple change but i can't seem to get there :s
Also, the code is currently selecting all of the data from the workbook that i open - is there a way i can select all of the data, excluding the first row (the first row contains the headings which i already have in Sheet2 in the active workbook).
[Code].....