I'm programing a VBA macro in Outlook 2007. I have added a button to the quick bar so when i open an email and push that button, a Excel file opens and i can input some data. Thing is, i need that if the opened email have some kind of attachment, when i push the button i want this attachement to be saved into a specific folder. Is that posssible?
View 2 RepliesI would like my macro to prompt the user to select a folder for files to be either opened from or saved to during the running of my macro.
In fact, I need them to select two folders, one for this month and the other for last month.
I think I need to set the two folders as a variable but being fairly new to vba I'm not quite sure how to do this.
I need a macro in a workbook to look at all the files in the same folder that have "*att*.xls" in the name and determine and copy from the range A15:W515 only the rows that have data in at least columns A, C and D. Each file will vary as to how many rows there will be and there are more than the files with "*att*.xls" in the folder. The data will be on the only worksheet in each file and the worksheet is named "G2WAttendee_xls" the data from all the files need to be copied to the file called "Consolidated webinar reports.xls" (I am using Excel 2003) and to a sheet called "Attendance Data" and added to the end of the last paste.
At the start of the macro the current file "Consolidated webinar report.xls" should be saved to a sub folder of the current directory and have the date saved added to the name. The sub folder is called "Completed reports". The data in the original file on worksheet "Attendance Data" should be deleted.
At the end of the process all the files that have had data copied from them should be moved to the sub folder "Attendance reports consolidated" (This could be done as each file is closed if that is easier).
I have headings in row 1 of the "Attendance Data" worksheet that match the headings in the various files in the folder (which will always be in row 14 of the individual "*att*.xls" files).
The folder with all the files and the "Consolidated webinar report.xls" file is at path "Z:P and S MEvaluationsWebinar series 2012-13TB".
I want to send emails to about 12 - 14 people with various different excel spreadsheets using macros. This is after I have finished running a macro that splits a report into various departments and needs to be emailed to the heads of each department. I want to be able to attach each ecel report to its owner and have the macro email it for me once the reports have been generated.
View 3 Replies View RelatedI have a large number of .txt files that are comma separated files (but not saved with the .csv extension) that I need to convert to files with the .csv extension.
Is there an easy way to do this for all files in a given folder? Ideally, the files don't even need to be opened as they are are quite large in size (70MB+) and there are a lot of them (500+).
I want to allow users to place files (.jpg, .tif, .pdf, .pps etc) into a network folder and then have my program open each file and display it for 30 seconds then close the file and the application before opening the next file.
1. Set up a loop to get a directory listing of the folder and write the listing to a text file.
2. Open up the text file and get the next filename in it
3. Open the file in the associated application
4. Wait for 30 seconds (or some period of time) and then close the file
5. Repeat steps 2-4
DoIt = 1
While DoIt = 1
Open "C:TempList.txt" for output as #1
Print #1, Files In Folder
Close #1
Open "C:TempList.txt" for input as #2
while not eof(2)
Line input #2, MyFile
Display MyFile on screen
Wait for 30 seconds
Close MyFile and MyApplication that opened it
wend
Close #2
I can get the directory listing just fine with no problems
I can open the files in the associated application just fine with no problems.
with closing the application after 30 seconds or some period of time.
I need some code that will allow me to easily send it a filename and it will know how to close the file and the application that opened it.
I have an Excel application (Excel 2003) which is stored in the default Program Files folder by the Installer; for example: c:program fileszxchello.xls. The problem I am running into is this file opens as Read Only in Vista and this is interfering with the running of the application. There is no problem opening the file normally in Windows XP.
I have been able to narrow down the cause of this to the User Account Control system in Vista - if I turn OFF User Account Control, the Excel file opens normally and my application functions normally. Is there another option to open the Excel file without turning OFF User Account Control because some users may find it unacceptable to turn OFF this security feature. Ofcourse, one option is to install the application in another location, outside the Program Folder, and the file would open normally, but the Packaging Wizard that I am using to package the application does not allow me to install the application in any other location and thus, the application installs in the Program Folder and I am running into this problem of the Exel file opening as Read Only. Is there a way out of this situation where I can open the file normally (not as Read Only)?
I am trying to import a list of outlook items into excel. These items are found in a folder somewhere within the vast network that is my organisations mail folder structure.
In my Outlook this folder structure looks something like this:
Myname@mycompany.com
- Inbox
- - Sub Folder
- Drafts
- Sent Items
Othername@mycompany.com
- Inbox
- - Sub Folder
- Drafts
- Sent Items
[code].....
I need a macro which will copy an image from a folder location and paste it into a mail (outlook).
I don't want the image to be added as an attachment.
To search the users signature folder for Microsoft Outlook see if the have any .htm files, if so list them in a message
Make the print find the default printer work out if it is black and white or colour and find the nearest opposite printer. i.e. if default is colour find nearest black and white
Protect a sheet but allow tabbing from certain cells to others.
Enable the users to use the workbook without having to rely on their macro settings being Low.
I is it possible to convert all comma separated text files in a single folder in to excel files. But the requirement is to have 2 sheets in each new file. first to be the full file - with all columns, and in the second sheet to keep only colum A B D G H K L M O P R S T V W from the first sheet. The second sheet name must be the same as the first one but without the first "wlist_" in the name.
One more thing. The third column in the second is called "COUL". there are short letters for colors in french
can they be converted with the sort in English like it goes:
NO = B
BA = W
RG = R
SO = P
JA = Y
BE = L
VE = GY
GR = G
VI = V
MA = BR
BJ = TA
OR = O
Here is a link to the both CSV and an example excel file with the end result. In this example i haven`t change the shorts for the colors. It takes me too much time with the find and replace function. And at the moment i`m really pushed from time.
[URL]
What I've got: This code allows the user to pick one (sub)folder - the MailItem properties of the folder are then printed to the sheet.
[Code] .....
My goal: Rather than allowing the user to pick one folder, I'd like to set up a hardcoded array of five subfolders found in a shared inbox. The hierarchy is as follows:
[Code] .....
The thing is, I have huge problems with referring to any of the red subfolders above. I think I'm able to point to the shared inbox if I substitute .Pickfolder with this, but then I'm stuck! I need to go down two levels in the hierarchy.
[Code] ......
I should be able to produce an array by myself so if I at least just could get a pointer on how to refer to one single folder that would be truly awesome.
I was able to create a macro that saves an email attachment based on the subject and then move it to another folder. I would like changing it to do the same thing only based one the sender’s email. I have it currently reading the save path and subject from the Excel worksheet.
[Code] .....
I have this code
View 10 Replies View RelatedI have a macro that saves the workbook after it has finsihed the rest of its formatting. I am finding it is saving in the wrong folder. I looked at my code and it is clearly states 'my documents' where I want it to save. Yet it keeps saving on a random folder on a shared drive.
View 8 Replies View RelatedI would like to copy a workbook from one folder to another without opening the original. Obviously, tempname.SaveCopyAs doesn't work, can you suggest an alternative?
Sub gettemplate()
Dim tempname, tempcopy As String
tempname = Application. GetOpenFilename 'Choose the path of the original workbook
tempcopy = Application.GetSaveAsFilename 'Choose the path where the copy will be saved
tempname.SaveCopyAs tempcopy 'Want to save the original workbook into the copy file path
Workbooks.Open tempname 'Open the copy
End Sub
I'm sure this will be an easy question, but I've been searching for awhile and can't seem to find an answer. I'm trying to write code in VBA that will check if a directory within c:documents and settings...my documents exists, and then save a file to that directory. The problem is, I'm having trouble finding the my documents directory. Is there some object or property that I could use to find the my documents path? Note that the worksheet that I'll be working with is in a separate directory, and therefore neither activeworkbook.path nor thisworkbook.path would be an option.
View 3 Replies View RelatedHow do I write a vba code to print active sheet to pdf, auto save in specific folder then mail using outlook
im using 'Nitro PDF Creator'
also id like it to automatically save the pdf as the same file name as the active worksheet and also auto enter the following when opening the email server:-
Address: Cell D18
Subject: Cell D21
Email body:
find attached quotation
Optional installation available on request
I currently have a application that people fill out on a userform. When they click on a command button at the end it puts all that information on a sheet i made and then we print it off. My objective is when it saves it to the sheet it also save a copy of that paticular sheet in a seperate location such as H:/applications. Also the copy should be renamed to the information thats in a specific cell Such as j4 the have this text added after that "Application"..
View 4 Replies View RelatedI would like excel to create the folder C:/Bill if it doesn't already exist when I click save/save as option and save the workbook in it with a filename that is a combination of text/values in cells A1 and A2 on Sheet1. Better if the save as dialog box appears with this option selected so that I have to just click Save in the save as dialog box to save it in "C:/Bill".
View 9 Replies View RelatedI have a macro which saves file to a specific folder. Problem is I can save the file only in my local drive C: but not on a server for some reason.
Here is the
At home in my home computer this code worked when C: was selected.
ChDir ("c:")
Title = Application.GetSaveAsFilename(Name, "Excel files (*.xls), *.xls")
ActiveWorkbook.SaveAs Name
Save the file in specific place
But when I substituted C: path with my server path it doesnt work! Have a look. Its the exact same code except my path is different.
ChDir ("\Lnf001Lnf1vol1SharedOP_ENGWork Order")
Title = Application.GetSaveAsFilename(Name, "Excel files (*.xls), *.xls")
ActiveWorkbook.SaveAs Name
I have this code that generates excel pages as pdf files, but I can't get it to save it in its individual folders. For example, I have the sheet name as Brian Lin, I want to save the pdf into the folder called Brian Lin, then if I have the sheet name as Lilly Tran, the pdf should save into the folder called Lilly Tran. Here's my code. The code is one step short of what I want it to do, it saves in the folder right before the name folders.
Code:
Sub Macro1()
Dim wsh As Worksheet, vWshs
Dim sFolderName As String, set_Path As String, name As String, hwas As String, sname As String, swsname As String
vWshs = Array("Rates", "Inputs")
set_Path = "C:UsersJonathanDesktopFaFundInvoices" & hwas & ""
[Code]...
Is there a code that will search a pre-determined folder for all .xls files containing the word "Temp", and deleting those files?
Example:
In the root of drive M, I have several Excel files. Anytime one of the original tracking logs is opened, a temp file of that log is automatically created. I would like to automatically search the drive and delete all files containin the word "Temp".
I've attached a picture of the directory tree, in case that will help. The file will always be named "PO Response Tracking - Temp#######.xls", with ###### representing a series of either 5 or 6 numbers.
Whenever I make a change to any of my spreadsheets and click save, a backup is created in the same folder. A sample document is attached. Download it, make a change, then save it.
View 3 Replies View RelatedI'm saving information from excel 2010 in to a text file (txt), when doing this and opening up the txt file the below line is saving it with " at the start and at the end of the line.
:32A:110809GBP4,00
Is there any way when saving as a text file without the " pulling through at the start and at the end of the line?
I currently have 3 batch files that I would like to be able to update using VBA instead of manually adding the information to each one. My goal is to be able to use my excel file and when I add new lines I could then run the script to save and/or update each of the batch files. I would like to be able to add the new lines I added in excel to the end of the batch files. I use the batch files for automation purposes but I do not like the fact that I have to open each one and added the same lines three times. This is why I want to be able to update my excel file and then add those new lines to the batch file. An example of how to accomplish this task using VBA would be awesome.
View 2 Replies View RelatedI've already done a search and found this on ozgrid.com:
RunCodeOnAllXLSFiles
I'm sure it is what I need, but as a non-programmer, I'm having trouble using it.
I have 12 files, each with 14 tabs. Example:
File: Sector.xls
Tab 1: Region 1
Tab 2: Region 2
Tab 3: Region 3
The client now wants 14 files, each with 12 tabs:
File: Region 1.xls
Tab 1: Sector 1
Tab 2: Sector 2
Tab 3: Sector 3
My first attempt was to try a macro:
1) left click on tab
2) move or copy...
3) move selected sheets to book (File: Region 1.xls)
4) then go on to next tab...............
Now, this does loop through the requested files, I can see them open, then close. I'm guessing they are copying, but I take it that I need to have it paste, then go to the next? (The c:files is just generic, as the files are on our LAN, and I'm just paranoid. The "Erie St. Clair" are the actual names of the file, and the tabs).
I want two sheets of my Excel workbook to be saved as txt file. The name of the file is compiled of a fixed part ("TrialList" or "BlockList") and two values from another sheet (subject and session number):
[Code] ........
Now, saving the text files with the desired file names works perfectly. However, Excel decides to change the names of the sheets that have been saved as text files accordingly. This is something I want to prevent from happening, since a second loop of actions will end because of the unexpected sheet names...
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] ........
I tried to use prtScrn button on the keyboard to capture the screen (excel UI) and then pasted it to excel then saved it. I tried to do the same thing on my friends machine and what surprises me is that the file size of two excel is different.
We use the same OS, excel version and the same machine specs.My saved excel file is around 7.91 mb in size while his is just around 235kb. Why is this like this?
Both pasted image have bmp as a format (default for excel when a you use print screen and directly paste it on excel.).