![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
peterbirksI solved a rather vexing Visual Basic problem today, despite being more than adequately dosed up with Solpadeine+ and Cuprofen (aka a cocktail of caffeine, codeine, paracetamol and ibuprofen). This evening I had a nap and decided to come off the drugs, just to see how well the muscles have healed up in 72 hours. This appears to have had a minor side-effect of disrupting my short-term concentration and of course I will finish the trip to Tesco later after the CD has stopped burning and I've got a high-score at Quick-fire snooker or was it Tetris?
But, seriously, I DID solve the Visual Basic problem, which had been irritating me for months. All that I wanted to do was create a macro that would extract the body of a document created in Word and then paste it into an Outlook email, while retaining formatting. Although the document would always be in the same folder, it would have a different name every day (that name being today's date). So, even if I could work out how to extract the text and formatting, I had to work out as well how to get it to know what file from which to extract the text and formatting.
This had defeated me a couple of years ago and I used a "kludge". I simply ran a macro in Word that formatted the text and then copied it to the clipboard. Then I ran the macro in Outlook, creating the email, the subject matter and the recipient list, and used the command SENDKEYS^v to put in the body text.
However, the popularity of my newsletter got to the stage where I had 1600 recipients and 20 distribution lists. One email was causing problems. So I split the distribution lists into two and created two emails.
Unfortunately, for reasons not even know to Microsoft, the SENDKEYS command is what Microsoft calls "not robust" and what ordinary humans would call "unreliable". Instead of fixing it, Microsoft just recommends that you find some other way to do things.
Needless to say, after that, you are on your own. The history of Outlook is a sad one, but rather typical of Microsoft. Suffice to say that it has never integrated that well with the rest of Office, and when it comes to Visual Basic, Outlook is in a world of its own.
I dare say that many in the computing world would see this as par for the course and would wonder what all the fuss is about. Meanwhile everybody outside the computing world looks at the mess that is Microsoft Outlook and sighs.
Anyhoo, I finally solved the problem today, helped by aforesaid Codeine, caffeine, paracetamol and ibuprofen. As with all things, it's easy (ish) to do once you know how, but almost impossible to get to that position without knowing how in the first place. By working from the end point to the start point, I worked out that I needed to change the word macro so that it saved the document in WordFormatHTML (in an added Microsoft quirk, this promptly calls the file .htm rather than .html).
I then created an input box in the Outlook macro asking the user to ype in today's date in a six-digit format. The macro then "added" the .htm to the end and the Path to the front.
So far, so good. All I then had to do was get the macro to extract the text. I found some code on the web that seemed to work, ran it, and voila. Success.
So, I now actually have a robust bit of Outlook code that can create a number of emails with different recipients and subtly different Body Text for each email (e.g., personalized for the recipient company), all surrounding the same core daily newsletter.
++++++++
Spurred on by this success, this evening I managed to get Facebook working on the Kindle. I also looked at a demo of Calibre (well, half-looked, since I was playing with the Facebook on the Kindle at the time). This looks like a seriously nifty piece of software for the Kindle, mainly for downloading The Economist!
My problem now is not getting material to read, but having the time to read it. I'm thinking of dumping Metro in the morning, and focusing on whatever book I have to hand. The problem is that at 5.50am, Metro is just about all that I can cope with. However, maybe if the Economist were on the Kindle, I could just about cope with a short article there.
++++++++++
Well, I have been off the painkillers for about 8 hours now. The back is a kind of dull ache, but much of my strength has returned (a couple of days ago I could hardly walk up the stairs). One person asked why on earth I put myself through such bodily stress. The asnwer is now clear. If I had done my back in a year ago by, say, lifting a box, there is no way that I would have healed as quickly as I have this week. Sure, better not to strain the back. But, as with motor racing, where you have to put the car off the track a few times to know how far you can push yourself, I learnt some valuable lessons this week without doing any permanent harm.
One is that, when going for big lifts, don't make a "big leap". I went straight from 145kg to 155kg. I should have put a 150kg in the middle of this and taken a five-minute rest before the 155kg. But I'd psyched myself up for 160kg, so I felt that 155kg wasn't a big leap. But it was.
http://tinypic.com/r/n4wly0/5
The second was that I need to do pilates, which will strengthen the horizontal muscles connecting the big muscles.
The third was that I DID THE LIFT. That showed that I had the mental strength. All I need to do is get the body to catch up.
If you watch the video slowly you will see a little left-side wobble half way up. That's when the big middle vertical muscle on the left side of my spine had a little spasm.
But, seriously, I DID solve the Visual Basic problem, which had been irritating me for months. All that I wanted to do was create a macro that would extract the body of a document created in Word and then paste it into an Outlook email, while retaining formatting. Although the document would always be in the same folder, it would have a different name every day (that name being today's date). So, even if I could work out how to extract the text and formatting, I had to work out as well how to get it to know what file from which to extract the text and formatting.
This had defeated me a couple of years ago and I used a "kludge". I simply ran a macro in Word that formatted the text and then copied it to the clipboard. Then I ran the macro in Outlook, creating the email, the subject matter and the recipient list, and used the command SENDKEYS^v to put in the body text.
However, the popularity of my newsletter got to the stage where I had 1600 recipients and 20 distribution lists. One email was causing problems. So I split the distribution lists into two and created two emails.
Unfortunately, for reasons not even know to Microsoft, the SENDKEYS command is what Microsoft calls "not robust" and what ordinary humans would call "unreliable". Instead of fixing it, Microsoft just recommends that you find some other way to do things.
Needless to say, after that, you are on your own. The history of Outlook is a sad one, but rather typical of Microsoft. Suffice to say that it has never integrated that well with the rest of Office, and when it comes to Visual Basic, Outlook is in a world of its own.
I dare say that many in the computing world would see this as par for the course and would wonder what all the fuss is about. Meanwhile everybody outside the computing world looks at the mess that is Microsoft Outlook and sighs.
Anyhoo, I finally solved the problem today, helped by aforesaid Codeine, caffeine, paracetamol and ibuprofen. As with all things, it's easy (ish) to do once you know how, but almost impossible to get to that position without knowing how in the first place. By working from the end point to the start point, I worked out that I needed to change the word macro so that it saved the document in WordFormatHTML (in an added Microsoft quirk, this promptly calls the file .htm rather than .html).
I then created an input box in the Outlook macro asking the user to ype in today's date in a six-digit format. The macro then "added" the .htm to the end and the Path to the front.
So far, so good. All I then had to do was get the macro to extract the text. I found some code on the web that seemed to work, ran it, and voila. Success.
So, I now actually have a robust bit of Outlook code that can create a number of emails with different recipients and subtly different Body Text for each email (e.g., personalized for the recipient company), all surrounding the same core daily newsletter.
++++++++
Spurred on by this success, this evening I managed to get Facebook working on the Kindle. I also looked at a demo of Calibre (well, half-looked, since I was playing with the Facebook on the Kindle at the time). This looks like a seriously nifty piece of software for the Kindle, mainly for downloading The Economist!
My problem now is not getting material to read, but having the time to read it. I'm thinking of dumping Metro in the morning, and focusing on whatever book I have to hand. The problem is that at 5.50am, Metro is just about all that I can cope with. However, maybe if the Economist were on the Kindle, I could just about cope with a short article there.
++++++++++
Well, I have been off the painkillers for about 8 hours now. The back is a kind of dull ache, but much of my strength has returned (a couple of days ago I could hardly walk up the stairs). One person asked why on earth I put myself through such bodily stress. The asnwer is now clear. If I had done my back in a year ago by, say, lifting a box, there is no way that I would have healed as quickly as I have this week. Sure, better not to strain the back. But, as with motor racing, where you have to put the car off the track a few times to know how far you can push yourself, I learnt some valuable lessons this week without doing any permanent harm.
One is that, when going for big lifts, don't make a "big leap". I went straight from 145kg to 155kg. I should have put a 150kg in the middle of this and taken a five-minute rest before the 155kg. But I'd psyched myself up for 160kg, so I felt that 155kg wasn't a big leap. But it was.
http://tinypic.com/r/n4wly0/5
The second was that I need to do pilates, which will strengthen the horizontal muscles connecting the big muscles.
The third was that I DID THE LIFT. That showed that I had the mental strength. All I need to do is get the body to catch up.
If you watch the video slowly you will see a little left-side wobble half way up. That's when the big middle vertical muscle on the left side of my spine had a little spasm.
![[identity profile]](https://www.dreamwidth.org/img/silk/identity/openid.png){kind=small}
no subject
Date: 2012-02-10 06:19 pm (UTC)no subject
Date: 2012-02-11 06:25 pm (UTC)PJ
no subject
Date: 2012-02-11 04:17 pm (UTC)More specifically, there are two problems with it. One: where are you sending they keys to? Presumably into the stream of other asynchronous events coming from the keyboard and (perhaps more significantly) the mouse, which will fire off events even if you just wave it around a bit or it shakes on the desk. You might think you're sending ctrl-v, but you have no way of knowing or even of testing. What you are most certainly doing is:
(1) Starting from an unknown global state, which varies according to what your computer is doing at this microsecond.
(2) "Sending" the control key ... somewhere.
(3) Hoping that no global state changes before (4)
(4) "Sending" the V key ... somewhere.
(5) Hoping that the result gets into the message pump for the application to which you are pasting before anything else gets in.
There are also race conditions and locale issues and permission issues and gawd knows what going on. It is not an atomic operation and therefore it is a klooge.
Glad to know it sort of worked for quite a long time, though. I find a certain sense of satisfaction in "beating the system" this way, although the system always comes back to bite me.
Oh, and Outlook? I don't know of anybody who likes Outlook, even inside Microsoft. It's better than the alternatives, which is saying nothing at all. And why it isn't properly integrated into VBA (or Visual Basic, if you will) is something of a mystery.
No, you are unimpeachably correct about Outlook.
no subject
Date: 2012-02-11 06:21 pm (UTC)So if I added in a SECOND SENDKEYS command (one for the Shift key) with the "wait = true" parameter, this seemed to help.
Well, it did on one machine, but not on another, ostensibly very similar, machine.
The new system not only eorks robustly, but it occurred to me that I could adapt it so that the content was sent to Amazon Kindles.
PJ
no subject
Date: 2012-02-12 02:58 pm (UTC)You'd need to ask Mike (or someone who actually knows this stuff), but I think what you actually want to do is to use OLE Automation (fully supported in VBA for obvious reasons). In theory (I am completely ignorant in practice) this should allow you to export the contents of the clipboard seamlessly into Outlook.
No criticism implied, of course: if your solution works reliably, why bother with the extra work?
Just a pointer in case something goes wrong somewhere down the road.
OK, "kludge" it is, then. It's a bit too Nathan Forrest for me, but whatever.
no subject
Date: 2012-02-13 05:06 pm (UTC)The "robust" solution doesn't use SendKeys at all.
And I brought in a new solution because I decided that I might want to create four, five or even more emails, with customisable contents (around the same "core" message.
At the risk of boring everyone to even more tears, here is the actual robust code as designed for one set of recipients. One can create more emails by adding a repeat of the code starting "With oMail", and changing the recipient list.
The input box asks you to put in today's date. The next bits use the paths to access that file, with the right suffix (so, today's file to import into the email is 130212t.htm).
I've now extended this so that I can send out the original 130212.docx file out as an attachment, or the 130212t.htm file out as an attachment, rather than as bodytext. That means I can send it to a distribution list of Kindle addresses, for example, or any other e-reader that can convert an htm file.
Of course, one other way round that for e-readers is to just convert it into a .pdf beforehand. Except that I hate .pdfs
Sub SEND_IIN_HTML()
On Error GoTo Sample1Error
Dim oMail As Outlook.MailItem
Dim oFSO
Dim oFS
My_Email_address = "peter.birks@informa.com"
Main_addresses = "TST SUB 1;TEST SUB 2C;TEST SUB 3;TEST SUB 4;TEST SUB 5;TEST SUB 6;TEST SUB 7;TEST SUB FRESHFIELDS;TEST SUB INFORMA INTERNAL;SUBSCRIBER RGA RE;KPMG;Argo;"
IIN_Short_Date = Format(Date, "MMM-dd-yyyy")
Filename1 = InputBox("Enter the name of the file (six-letter code)that you wish to send", "Send File", , 5000, 5000)
Filename2 = Filename1 & "t.htm"
FileNamePlusPath = "S:\Professional Insurance\Evandale\NewsLetters\SEND_EIN\" & Filename2
If Application.ActiveExplorer.Selection.Count Then
If TypeOf Application.ActiveExplorer.Selection(1) Is Outlook.MailItem Then
Set oMail = Outlook.CreateItem(olMailItem)
With oMail
.To = My_Email_address
.BCC = Main_addresses
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFS = oFSO.opentextfile(FileNamePlusPath)
stext = oFS.readall
oMail.BodyFormat = olFormatHTML
oMail.HTMLBody = stext & vbCr & oMail.HTMLBody
oMail.Display
End With
Exit Sub
Sample1Error:
MsgBox ("There has been a cock-up. Sorry. Either try again or send it manually.")
End Sub
no subject
Date: 2012-02-27 10:01 pm (UTC)Don't give up the day job, though. It's hell out there in programming land, hell, I tell you, lad ... now, where did I put me scrunglings? Eeeh-awwrgh!