How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask swmcdonnell Your Own Question

swmcdonnell
swmcdonnell, Computer Scientist
Category: Software
Satisfied Customers: 233
Experience:  Steve has many years of experience in software development.
47680681
Type Your Software Question Here...
swmcdonnell is online now
A new question is answered every 9 seconds

I'm running a script in outlook to download an attachment

Customer Question

I'm running a script in outlook to download an attachment and save it to a shared drive folder. I am using a combination of an outlook rule combined with a vba script. This combination works great. The only issue that I have is that I need the script to convert the attachment to another excel format. Currently the email is sent as a .xls format file. I need it to download into the folder as a .xlsx format file. Below is the script I currently have. Can you please help me modify the script so that the saved file is in the .xlsx format. I tried just changing the extension in the name but that does not work. Please help.
Current script:
Public Sub saveAttachtoDisk(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
saveFolder = "\\mlsflsrv.med-lab.com\data\Operations\Macro References"
For Each objAtt In itm.Attachments
objAtt.SaveAsFile saveFolder & "\DailyOpsAuditingList.xls"
Set objAtt = Nothing
Next
End Sub
Submitted: 1 month ago.
Category: Software
Expert:  Pete replied 1 month ago.

Hello,
I am Pete, and I'll be happy to assist with your question today.

Customer: replied 1 month ago.
Thanks Pete, looking forward to it
Expert:  Pete replied 1 month ago.

You can use the following code to convert the file format from .xls to .xlsx:

Set Workbook = Excel.Workbooks.Open("\DailyOpsAuditingList.xls")

Set Worksheet = Workbook.Worksheets(1)

Workbook.SaveAs "\DailyOpsAuditingList.xlsb", xlExcel12

Customer: replied 1 month ago.
Can you please paste it into the code where it belongs? Also, I noticed you used a .xlsb in the code. Will this produce the .xlsx result?
Customer: replied 1 month ago.
Pete, the code you sent me is not working. Can you please send it to me in the code I sent you?
Expert:  Pete replied 1 month ago.

.xlsb is a binary file format (the B stands for Binary). The binary file size is generally smaller than a .xlsx file and therefore opens and saves much faster than an .xlsx file. You can use .xlsx if you prefer.

What error are you getting when you use the code?

Customer: replied 1 month ago.
Ok thanks. Yes, I prefer to stay with .xlsx. I basically getting run time errors because I don't know where to insert the code you sent me. Ive tried several different ways. Can you please revise the code you sent me for it to be .xlsx and send me your additional code inserted into the code I am using below so that I do not get run time errors? Thank you.Public Sub saveAttachtoDisk(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
saveFolder = "\\mlsflsrv.med-lab.com\data\Operations\Macro References"
For Each objAtt In itm.Attachments
objAtt.SaveAsFile saveFolder & "\DailyOpsAuditingList.xls"
Set objAtt = Nothing
Next
End Sub
Expert:  swmcdonnell replied 1 month ago.

Hi there, my name is***** can help you get this done. Hold on a few minutes and I'll send you back the right code to use.

Expert:  swmcdonnell replied 1 month ago.

Ok, here's what you need to do:

  1. Open the Visual Basic editor in Outlook.
  2. Choose Tools and then References...
  3. Locate Microsoft Excel 15.0 Object Library, put a check next to it and click OK.
  4. Click here to access the VB script. Copy it and paste it into your Outlook.
  5. Run and test it. Please see my notes below.

The macro assumes there is only one attachment. You had a loop in your version above, but it wasn't making any kind of distinction in the loop, so I changed it to take the first attachment. It checks to make sure it has an xls extension or it won't run.

The macro saves the file with its new name and location, but its old format. Then it opens Excel, opens the workbook and saves it under the current format. Then it deletes the original file (xls) that it saved from Outlook.

Please let me know if you have any questions or problems.

If I've answered your question and resolved your issue, can you please accept the answer and rate me 5 stars? I don't get paid anything for helping you unless you accept the answer If not, please chat me back and I'll get you the information you need to resolve your issue. Thanks and have a great day!

Customer: replied 1 month ago.
Hi Steve, thank for the reply. I followed your instructions but cannot get the script to work for some reason. One item that is different from what you sent me is that I enabled Microsoft Excel 16.0 Object Library instead of 15.0 as you had instructed. I only had the 16.0 version. I got the previous code to save the file in the shared drive location by combining the script with the rule so I believe the rule setup is good to go. With the script you sent me I do not get any errors or messages, it doesn't give me any indication that it is running. What do you think? I am attaching a screen shot of the code in visual basic that I copied and pasted from your message. The "End Sub" is missing only because I couldn't scroll down.
Customer: replied 1 month ago.
Why am I being referred to another person again?
Expert:  swmcdonnell replied 1 month ago.

Pete opted out, which means he quit because he didn't know how to solve your issue, so I picked up the question because I can help you.

The version of the Excel object library really shouldn't matter. I know it works because I tested it here, so we just need to figure out what's different between our setups.

Can you give this a try: Open an email with an attachment and run the script with the attachment open. If it still doesn't do anything, open the VB editor, click on the first line of the macro and press F8. You'll see a yellow bar start to go step-by-step through the program each time you press F8. Then we can see what's going on. If I'm online when you check, chat me up and maybe we can look at it together.

Customer: replied 1 month ago.
Hi there, thanks. I'm online right now just in case you're available. I think I will need your help because I'm not versed enough to do the items that you're asking me to try. I tried manually running the script rule but again nothing happened. It may be something simple but I cant figure it out.
Expert:  swmcdonnell replied 1 month ago.

hey are you there?

Customer: replied 1 month ago.
Can be available in 1 hour if that works for you. Driving home from work. Can you join then?
Expert:  swmcdonnell replied 1 month ago.

Yeah, that should be ok

Customer: replied 1 month ago.
Ok thanks, ***** ***** you in 1 hour.
Customer: replied 1 month ago.
I'm available now if you are.
Customer: replied 1 month ago.
Posted by JustAnswer at customer's request) Hello. I would like to request the following Expert Service(s) from you: Secure Remote Assistance. Let me know if you need more information, or send me the service offer(s) so we can proceed.
Customer: replied 1 month ago.
Hi Steve, please let me know if we need to reschedule so that I can get some things done away from the computer. I'm waiting for you on the computer. Are we still on?
Customer: replied 1 month ago.
are you available?
Expert:  swmcdonnell replied 1 month ago.

hey there, sorry i got tied up with someone else...

Customer: replied 1 month ago.
I got 30 min now. Still there?
Customer: replied 1 month ago.
Hey Steve, sorry to bother you so much. If there is any way to close this today, I would greatly appreciate it. I'm running out of time.
Expert:  swmcdonnell replied 1 month ago.

Hey there, I'm sorry I was helping other people and am just now able to get back to this. Can you give me a time that you'll be available on Monday and we can both be on at the same time?

Customer: replied 1 month ago.
Sorry Steve, the combination of Pete and your response time was too long without getting an answer that worked. I tried multiple times to reach out to you and also to pay the extra fee to get remote support. I realize that you are very busy but I needed this resolved sooner. I don't need this answered any longer.