Email And Save Worksheet As PDF With Current Date
Nov 6, 2009
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
View 9 Replies
ADVERTISEMENT
Nov 6, 2009
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]....
View 9 Replies
View Related
Oct 25, 2012
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.
View 7 Replies
View Related
Jun 26, 2014
I have the following code in VB from somewhere. How to modify or adding from the following codes.
(1) Is anyway I can modify sedning out PDF instead of the actual workbook?
(2) Is anyway I just want the range from (A1:G80) instead of the whole sheet for printing (PDF).
View 3 Replies
View Related
Feb 15, 2010
I want to schedule a print, for lets say every Sunday at midnight. I have managed to find some what of a solution by using the following vbscript and scheduling a print to default printer. Works perfectly if that is all I wanted.
Dim objExcel, objWorkbook, strFile
' Connect to Excel
Set objExcel = CreateObject("Excel.Application")
strFile = "C: est.xls"
' Open the Excel File for Printing
Set objWorkbook = objExcel.Workbooks.Open _
(strFile)
' Print to Default Printer
Set objSheet = objExcel. ActiveWorkbook.Worksheets(1)
objSheet.PrintOut
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
I do not want to print to an actual printer, therefore if I set my default printer as the document writer, the vbscript gets it as far as the "Save As" page. Is there a way I can add to this vbscript so it will save as well? Can I get it to save as "name"&[DATE], thereby having it save as a different name each time?
Can I change this vbscript to print to a named printer rather then a default printer? I am sure a PDF convertor may be of some assistance with this, but I do not have an PDF converter on my computer, and as this will be carried out on a computer that I will not have administration rights, I can not download one.
View 2 Replies
View Related
Jun 22, 2007
I'm trying to save a file with current date and time...I used the code from the following thread:
Save File Name With Date/time Stamp
ActiveWorkbook.SaveAs Filename:="C:FinalOutput.xls " & _
format(Now(), "mm_dd_yyyy hh mm AMPM"), FileFormat:=xlNormal, Password:="", _
WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Now when I try to run the macro, it gives me an error saying: Compile Error, Wrong number of arguments or invalid property assignment. And it highlights "format" in yellow.
View 9 Replies
View Related
May 5, 2006
I am using a module to create and save a Word document from an Excel module. I'm using the following line to save the document which works fine but (and this is a big BUT)(to quote a TV programme from long ago) obviously everytime I save the document it will overwrite itself. Is there a way of adding the date it is saved on to the file name so it creates a new document each time.
objWordDoc.SaveAs Filename: = "WAGReport"
I have tried changing it like this but get an error message
Dim dtMyDate As Date
dtMyDate = Format(Date, "DDMMYY")
dtMyDate = Sheets("Client Master").Range("d2")
objWordDoc.SaveAs Filename: = "WAGReport" & dtMyDate
View 3 Replies
View Related
Jul 5, 2009
I have an excel file which I use when I book keep journals in work. Each time I come across a new piece of bookkeeping I add to the file, name the sheet and index it. (I've attached a loose example for illustration) so the bookkeeping template.xls gets bigger every day.
Currently, when I book keep on a particular day, I open the template.xls workbook;
draft my journals;
and then manually copy the worksheets I have completed during the day from the template.xls workbook and paste the sheets into a new book and save the days bookkeeping as the current date.
With the file I have attached can someone show me how to write a macro and apply it to each of the buttons on the various work sheet tabs (each button will run the same macro).
Upon clicking a button, I would like the macro to;
Copy the active sheet into a new book and save the file as the current date.
Each subsequent click of a button the macro will check if the .xls file exists for the current date, if it does, then it will just copy the active worksheet to the file that has already been created.
In the new file which is saved as the current date, I need to remove the macro button and the hyperlink that reads back to index.
I have had some helpful macro's provided from another forum, however, the other forum does not appear to enable users to upload files, so I can't quite convey what it is I am trying to achieve.
View 14 Replies
View Related
May 6, 2014
I have this existing macro which saves each tab into a separate excel file. However, I'd like for it to also save them together in a new folder using the current month and date (named: QA Files May_05.06.14). The month and date would change according to current month/date. How would I incorporate that into this code? I am not good with macros.
Sub tabname()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
ws.Name = Range("D1").Value
Next
[Code]...
View 4 Replies
View Related
Mar 11, 2014
am using Excel 2010 and having issues trying to save a worksheet to a specified file location with the save date....
I have tried several posts form this forum and elsewhere and can't seem to get the macro to do what I want.....
I want to save a 'worksheet' from an open workbook that I use for updating information to the same file path as the workbook with the date the file saved...
View 6 Replies
View Related
Oct 10, 2011
VBA coding for automatically saving an excel file as another file using the current date as part of the file name together with "32ga" as a constant add-in. I also what this macro to run at a particular time of the day let say 00:20hrs. The excel file i want to save as is always open . It has data that changes every 24-hrs.
View 1 Replies
View Related
Mar 2, 2009
I currently have a button then when pressed automatically sends a summary report taken from the first page of Sheet 1.
Worksheet needs to be protected all the time, but Macro only works on an unprotected worksheet.
I was wondering what additional code and where to put in so that when
protected back again after Macro has been executed?
Here’s the Macro taken from [url]
View 11 Replies
View Related
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
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
Aug 15, 2014
I am trying to create a macro to run from a form button, within a report, to save a file to a variable file path and name depending on the date value in cell B5.
The format of B5 looks like - 13/08/2014 16:39
The file path has folders for each year in format "yyyy" with each year having sub folders for each month in format "mm".
The file name is just the date only and is formatted "dd.mm.yy" e.g. 13.08.14
I have tried the code below in various permutations but always end up with an error - Method 'SaveAs' of object '_Workbook' failed.
[Code] ......
View 3 Replies
View Related
May 15, 2013
I am trying to use VB to save a workbook into the same directory but with a file name that references the folder it is stored in...if that makes sense!
Here is where I have got to so far but fails on save,
Sub Rename()
Application.DisplayAlerts = False
mdy = Month(Now()) & "." & Day(Now()) & "." & Year(Now())
fold = ThisWorkbook.Path
FName = fold & "-" & mdy & "-(New).xls"
SaveName = fold & "" & FName
ActiveWorkbook.SaveAs (SaveName)
Application.DisplayAlerts = True
End Sub
View 3 Replies
View Related
Feb 23, 2014
I've set up a series of linked spreadsheets that feed data to an overall sheet. I work in a school, and each day the staff fill in student data, which then updates back into my overview spreadsheet.
Now the term has finished, I want to be able to store the data, then wipe the spreadsheets so they can be used for the new term (entering student data each day).
(There are about 20 different files I want to save, and don't want to have to copy/paste values for each sheet!)
View 4 Replies
View Related
Apr 23, 2009
I'm running a macro that intially opens an input box where you specify the filepath and name of a .ped file to import into the macro. The default path of H:BOM2.ped may not always be the path I use. It may be just H:.ped or H:Bom1.ped.
HTML INITIAL CHECK
Response = MsgBox(".............do you want to import BOM data?", vbYesNo)
Do Until Response = vbNo
'SELECT RAW FILE
Dim Message, Title, Default, MyValue
Message = "Please Enter PathFile Name for Source File"
Title = "Create CHINA INSPECTION REPORT(s)" ' Set Title.
Default = "H:BOM2.ped" ' Set Default.......
View 9 Replies
View Related
Jun 2, 2009
I have a form button I am currently using to save the workbook. However due to the workbook being so large is taking a little longer than I want to save. Is there a way to change the macro to save the current sheet only?
Sub Save()
'
'
'
ActiveWorkbook.Save
End Sub
View 9 Replies
View Related
Mar 9, 2014
I'm trying to workout how to take a known initial date a repeating frequency and work out the next due date from today.
Example
Initial Date :- 1st of January 2014
Frequency :- every 5 weeks
Current Date :- 9th of March 2014
Next Due date should be :-12th of March 2014 (if I worked it out correctly from my paper calendar)
I want to use a cell formula to do this for different initial dates and varying frequency periods (the frequency will always be whole weeks i.e. 1,2,3,4,5,6,7,8,8,10)
View 4 Replies
View Related
May 20, 2009
I am using the following code to send emails, it's from Ron DeBruin's site. It works, but how can I edit so that it doesn't send the file, but saves it to your drafts??
View 3 Replies
View Related
May 8, 2009
I have the code that saves it a a PDF, but a nicety would be to email it as well: -
inputfileSaveName = Application.GetSaveAsFilename(fileFilter:="PDF Files (*.pdf), *.pdf")
'export it - works just fine!
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=filesavename, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
'OK up to here!
Application.Dialogs(xlDialogSendMail).Show arg1:="", _
arg2:=EmailTitle, arg3:=Receipt
It now sends the "workbook" as an attachment and not the PDF..
How do I remove the attachment and attach the PDF Ive just created?
View 3 Replies
View Related
May 7, 2007
I am using the below code to save & email a report that is needed for a variety of different locations. I would like to be able to automate sending to multiple branches a branch specific report with some sort of a loop but I am not sure how to do it. So far, I have the below code that works for saving & emailing to one branch, however I was wondering if someone has an idea on how to loop the procedure and send to multiple branches? For example, if I have 3 branches 3 different managers:
Here is the code so far:
Private Sub emailreport_Click()
Password = "nohs1"
ActiveSheet.unprotect Password
On Error Resume Next
Dim wb As Workbook
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb ....................
View 9 Replies
View Related
May 30, 2012
I've been searching for code that will perform the following:
1. Save the current workbook then,
2. Save the current sheet as a tab delimited file to the desktop with the same name as the current workbook then,
3. Return to .xlsx file format
The file saved to the desktop can be overwritten each time the macro is run.
View 4 Replies
View Related
May 4, 2006
I get an xl file sent to me every day which I upload into a database. I then save the file keeping its name and save it into a selected folder. Is it possible to add a macro at the beginning of my current macro that saves the file using its name to a selected folder?
View 4 Replies
View Related
Jul 14, 2014
Here is a macro I am using to save a workbook using 'Save As' .
I have it open a template workbook we use, and then perform a save as, pathed to a specific folder on the desktop. We do this every night just before midnight.
What I haven't been able to figure out is how to have it save to the next month folder (The folders with the name of all the months are already created) on the last day of each current month.
For example. In my code, the final folder in the path is the current month July. On July 31 when I run the macro, I would like it to save it in the folder called August.
Is there a way to code this so it will recognize what the current month is and then at the end of each current month save it in the next month?
[Code] .....
View 12 Replies
View Related
Feb 28, 2014
I want to take the current sheet and copy to a new workbook (keeping the tab name) and then save as the tab name (xlsl) and close.
I've found ones that do a Saveas box or ends up saving as "Sheet1," "Sheet2" etc but can't seem to find one that keeps tab name and saves.
Here is the one I was using, but it saves all tabs not just selected:
Code:
Sub CopySheet()
Dim fname As Variant, ws As Worksheet, wb As Workbook
Dim nCol As Integer, iCol As Integer
[Code].....
View 1 Replies
View Related
Dec 4, 2007
I recorded a macro to save a workbook to a specific folder using the current file name. However, I've tried to remove the absolute reference to a specific filename but it is not working for me. Am I close?
ActiveWorkbook.SaveAs Filename:= _
"H:Files and DocumentsPROJECTSSR 2 SS Phase Two Component ReliabilityWinTrac Files xt_conversionsexcel_version" & SheetName _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
View 2 Replies
View Related
Oct 14, 2008
I have a worksheet with 30,000 rows. But sometimes even if I have fewer records in this worksheet(lets say 1000) worksheet shows the same 30,000 rows.And its annoying when you try to navigate using vertical scroll bar. Is there any option to re-adjust the worksheet to make it more user friendly based on number of rows in current sheet.
View 2 Replies
View Related
Jun 12, 2007
In cell A1, I have the month number (eg, 1, 2, 3,). The month number reflects current month and will automatically change with every month. For example, right now it’s 6, next month it will automatically change to 7. Each two columns in Range A10:X20 represents the data from January to December. I want to use a worksheet event to change the background of the current month two columns in the range to yellow color and the two columns in the range will be visible when I activate this sheet.
View 3 Replies
View Related
