Enter data and send file as email

[FDibbins]

I have a file in which pretty much nothing changes. All I do, is open the file, enter today's date (cant use =TODAY), then email the file to some-one. I am trying to automate this, and this is what I have so far...

Please Login or Register  to view this content.

I initially tried using CTRL : to enter the date with the macro, but it just entered the date itself.
Also, I want it to enter the email addy by itself as well. I was using my own as a test, but it didnt get put into the macro...dibbins, william (its a company addy, so the rest gets put in by outlook)

[TMS]

Please Login or Register  to view this content.

Regards, TMS

[FDibbins]

Trevor, thanks for that
How do I get it to enter the email addy automatically? It's always the same addy, and only 1 addy

[humdingaling]

Please Login or Register  to view this content.

This method from Ron - automatic email address/subject/body
replace .display with .send for send without preview

http://www.rondebruin.nl/win/s1/outlook/amail1.htm

[FDibbins]

I have not forgotten about this, just have not yet had chance to test/try it yet

[FDibbins]

OK, I had chance to get back to this now

When I try and run it, I get error message saying "Cannot rum macro "doors". The macro may not be available in this WB or macros may be disabled"

I stored it in my personal.xlsb, which is hidden. It works fine when my pers. is not hidden, do I need to keep it unhidden?

[humdingaling]

isn't personal.xlsb normally hidden?
or are you talking about a different kind of hidden?

[FDibbins]

macro's are not my strong point, so I dont really know - I know mine is hidden, and it wont run unless I unhide it

[romperstomper]

How are you running the macro? (you didn't call the module doors too did you?)

[FDibbins]

I put an icon on the ribbon
(this is for work, at work, but I am at home right now)

[FDibbins]

OK, back again.

This is what I did.

I have this macro in PERSONAL.XLSB...

Please Login or Register  to view this content.

Thate the code from here.
I then copied that to my PERSONAL.XLSB and it ended up in Module3 (Code) (Thats what it says at the top of the PERSONAL.XLSB window)
Then I went into QAT/More Commands/Macros, found Doors and added it to the ribbon

Thats it.

If I keep PERSONAL.XLSB unhidden, it works, if I hide PERSONAL.XLSB, it does not work
What am I doing wrong?

[romperstomper]

I can't replicate that. This is in 2010? What are your Trust Center settings for Add-ins and Macros?

[FDibbins]

Im using (at work) 2007, Trust Center Settings are "Enable all macros" and "Trust access to VBA project object model" is checked

(sorry for the delay in replying, I dont use this every day, and then it slips my mind )

[humdingaling]

so i'm guessing your hiding the macro for security reasons

cannot you do the opposite to what is posted at this website then?
http://excelribbon.tips.net/T012159_..._Workbook.html

have a Event trigger on your workbook to automatically unhide it
ie it can only be run when you open certain workbook?

[FDibbins]

having my PERSONAL.XLSB unhidden is not really a problem, I can keep it "open", I just thought it was better to keep it hidden. It opens now whenever I start excel - again, not a huge biggie

[ELeGault]

Ford,

Check out the file I attached. This may be a solution to automating for you.

Go through the VBA and fill in the appropriate fields in the email, such as the Recipients, Subject, Body, Signature and try it out.

I use this as a basic email kick off file for a lot of things but have tailored it slightly to what you are doing, such as in Process() to set the date.

This file will allow you to set a Task Schedule through Windows to open the file at a desired time, and then this will kick off shortly after (Providing you set the time in "This Workbook" to be shortly after) There is a way to use a counter, but I find setting a specific time runs better personally.

In any case... check it out and let me know if you run into issues and/or if you are set with the current process. I do keep this file separate so that I can tailor the template to fit any file I need and name it a different Kickoff name -

Hope it works for you,

ELeGault - Example Email Kick off File.xlsm

Cheers

Eddie

[FDibbins]

Eddie, thanks a ton for this

I am not at work right now (off for 3 days). The last 1 above does work well, but my Pers...xlsb WB needs to be open

I will take a look at your suggestion on thursday

[ELeGault]

Had a chance to test this out? - Not sure if I left anything open in the template - seemed to work when I messed with it but curious if it was easily adopted for testing at the very least -

Cheers

[FDibbins]

Eddie, no, sorry. I dont check it often enough, then it slips down the list and gets lost. I will email myself at work to make suree I check it.

Thanks again

[ELeGault]

Ford, No worries - Just curious how easily adopted it is in its current state and/or if I need to change more to make it more tangible to share as a viable email solution/kick off file -

Cheers -

[FDibbins]

I have looked at your file, looks like you put a lot of effort into that. However, it is way above me.

What this is for, is...
We have a excel file we open, update and email to 1 individual.
The situation has got to the point where there is now no more updates to be made, so we just open, change the date, and email.
The macro that I have been given works perfectly. I have added an icon to the ribbon, and 1 click, that quick. The ONLY problem (and it's not really even a problem), is that my personal.xlsb needs to be open.

I had thought to put the macro INTO that file that we send, but obviously that file needs to be open for teh macro to be available.

And when thinking again, I have a workbook with a bunch of formula examples that I regularly use when on the forum. I could probably just add all those sheets to my Pers.xlsb file and that would then auto-open and eveything will be in 1 place

[ELeGault]

So when I have time - if you want we can manipulate this to run your macro - the method is intended to make it so Windows will open the "Kick off file" then at a set time after that the kick off file will open any workbooks you desire (your case one) and run a macro (Again, for you sounds like a straight Email) - and then the kick off file will upload/save save copies and then close out the application entirely - all while you are snug as a bug in rug at home barely waking up to come into work...

You can probably do this with the file I gave you, just get rid of all the macros in there (Except maybe the Email, but even that you may have your own tailored to what you need) - Change the time and macros to be run on "ThisWorkbook" and then schedule the kick off file to run from Windows Task Scheduler. Maybe alter the save_Quit macro to save the file and then Quit application -

Either way - It is a file I have put some time into - (Added notes and got rid of a bunch of other template stuff like creating an image to attach as well) so that it would be easier to read for you - but this is something I use a lot to keep the actual data books free of macros as Excel tends to bug out on occasion forcing me to restore older versions... Only happens on Macro enabled books, so when it does nothing is changed from the current book to the old one

Alright hitting the hay, been a long weekend and its going to be a long week -

Cheers!

[FDibbins]

The code I have works great, doing exactly what it needs to, and does not need to be changed.

My question remains that my personal.xlsb file needs to be open/unhidden. Is that a normal situation, or am I doing something wrong?

[jaslake]

Hi Ford

I'm following this Thread for personal edification/education as I'm a novice at Personal.xlsb. I don't use Personal.xlsb, except, on occasion, I've created it for some OP Issues.

My question is this...I'm of the impression Personal.xlsb opens EVERY time you start Excel. So, I'm confused by this

The ONLY problem (and it's not really even a problem), is that my personal.xlsb needs to be open.

Does your Personal.xlsb NOT open when you start Excel?

[FDibbins]

It does, but I have to have it as unhidden. If is it hidden (and presumably open?) then the code wont run
I am a VBA noob, so know pretty much nothing about this

[romperstomper]

The short answer is that no it's not normal. Personal.xlsb should be hidden and the icon on the QAT should work. What exact build of 2007 are you running?

[jaslake]

@Rory

I'll continue to follow...

for personal edification/education

Ford is in your capable hands...thanks.

[FDibbins]

Rory, Im trying to find that. This is on a work network, and it doesnt show. It is Office 2007, I know that, but I cant find the build anywhere.

What I have found is this...
File version (excel.exe) 12.0.4518.1014
product version has teh same number

[romperstomper]

If you open Excel Options, and click the Resources section, what does it show in the 'About Microsoft Excel 2007 part for Service Packs? My build is 12.0.6718.5000 SP3 so I'd say yours is missing a few updates. SP2 (which is pretty much essential for 2007 to behave even vaguely like it should) would give you a build number of 12.0.6425.1000, so I suspect you do not even have SP1 (in which case your IT department should be summarily dismissed!).

[TMS]

Just following Rory's instructions, for information, and maybe to prove the point, mine shows Microsoft Office Excel 2007 (12.0.6718.5000) SP3 MSO (12.0.6718.5000)

I've never felt the urge to use a Personal.xls/xlsb file so I've never encountered these issues.

[FDibbins]

Rory, thanks again. My "weekend" is Mon/Tues, so Im at home right now - I can check again on Wed. If its not there, not much I can do about that

Trevor, keep in mind Im a mega noob with VBA, if you (I) dont use Pers.xlsb, how would you (I) keep code that would work from anywhere in excel?

[TMS]

I'm mostly doing things for other people so having stuff in MY personal workbook wouldn't be helpful to them.

What sort of things do YOU want to put in there? An alternative, I guess would be to create an Add-In but, again, I'd need to ensure the Add-In was available.

I find it easier to have standard modules, where I need them, and copy them between applications. A lot of the time it is re-using standard processes and procedures tailored to a specific need.

[FDibbins]

Well for this specific situation, it's at work, and I could be working on any 1 of 4-5 different pc's, all on a network.

What this is for, is...
We have a excel file we open, update and email to 1 individual.
The situation has got to the point where there is now no more updates to be made, so we just open, change the date, and email.
The macro that I have been given works perfectly. I have added an icon to the ribbon, and 1 click, that quick. The ONLY problem (and it's not really even a problem), is that my personal.xlsb needs to be open.

And when thinking again, I have a workbook with a bunch of formula examples that I regularly use when on the forum. I could probably just add all those sheets to my Pers.xlsb file and that would then auto-open and eveything will be in 1 place

hmm or then again, I could just add the code to my formula workbook. I think that would solve all problems - I generally have that open anyway.

Still puzzled regarding needing the pers wb unhidden though (more curious than problematic)

[TMS]

As I say, in a very long time, I haven't felt the need to use one. I guess that, if all the Excel spreadsheets I develop and maintain were on my computer, it might be useful to have some common routines. But, as it is, I find it easier to make the workbooks stand-alone. I don't even want to experiment

That said, I have one client where I use some macros to merge a couple of files for them. It's a bit of a mix of manual process and running macros. I don't want to put the code in either of their workbooks, so I have created an "application" workbook and all the code I have created (and am still trying to improve) sits. I open the application workbook first, then the two workbooks. I copy and paste transactions into a sheet in the "Master" workbook and then close the secondary workbook. Then, from the Master workbook, I call the macros to do the merging, etc. I then save their Master workbook and send it back to them.

For me, that's just as easy as putting the very specific code in a personal workbook or creating an Add-In, though I might have a play with that.

[jaslake]

Hi Ford

My Build is also Microsoft Office Excel 2007 (12.0.6718.5000) SP3 MSO (12.0.6718.5000)

I've created a Personal.xls (OP is using Excel 2003) for an OP to test some Code I'd written for the OP. The Code works as expected with the Personal.xls hidden.

I suspect Rory may well be on the right track.

[romperstomper]

Am I right in thinking you can run the macro from the macros dialog with Personal.xlsb hidden? If so, have you tried assigning it a shortcut key to see if that works while it's hidden?

[FDibbins]

Rory, I think I recall doing it that way could (through the macro window), but as I said, Im not at work right now.

(I really appreciate all the assistance on this)

[FDibbins]

OK, I just hid my Pers WB, and now cannot (again) run the code from the Ribbon.

Interestingly though, when I tried to run it from the Macro-Run window, I got a warning popped up, waring that a prog was trying to send an email on my behalf (never seen that 1 before) (see attached word doc.)

When I clicked Allow, it sent with no problems

The build is 12.0.4518.1014

[romperstomper]

I'm pretty sure it's a bug fixed by a service pack. I'll try and test tomorrow.

Are you using the code that sends automatically now? That would trigger that message.

[romperstomper]

Duplicate post

[FDibbins]

Yes I am. I am at home right now, I will copy the code here when I get to work today.

I had another thought about that - I have your BB copy code in my Pers. file too. That seems to work fine whether the file is hidden or not

[FDibbins]

OK, here is the code that Im using in my Pers file...

Please Login or Register  to view this content.