Jump to content
Macro Express Forums


  • Content Count

  • Joined

  • Last visited

  • Days Won


terrypin last won the day on December 31 2019

terrypin had the most liked content!

Community Reputation

8 Neutral

1 Follower

About terrypin

  • Rank

Profile Information

  • Gender
  • Location
    East Grinstead, West Sussex, UK

Recent Profile Visitors

860 profile views
  1. Maybe some sort of time zone issue? If you could post a minimal scheduled macro that causes that behaviour we could try to reproduce. FWIW I regularly use scheduled macros for important reminders. I've never changed the frequency from its default of 10 s, but I don't see that notification. P.S: It's Valentine's day tomorrow!
  2. Interesting, thanks Pete, I'll explore that alternative more thoroughly to see if I can get a simple example working. But from initial study it looks a fair bit more complex than my usual method of launching a VBA macro from an MEP macro, namely with Alt+F8 as discussed up-thread. However it opens up a possible new approach to transferring data between Excel and MEP that I'd never been aware of before. I've been using either - the clipboard - or reading an intermediate (and otherwise redundant) text file. So many thanks for the heads up!
  3. That's very kind of you, Cory, thank you, but I'm going to pass. My list of 'things to learn' is not getting any smaller. So I try to resist most new temptations. Right now I'm doing my best to move beyond my 'google/copy/paste/edit' mode of writing Excel VBA macros. Never having learnt any object based programming language, that's struggle enough for now. And I also need to get back to some practical electronics in the shed workshop to continue my Arduino programming. 😉
  4. Pleased to hear it Brian. Still waiting to hear from Naresh - from nearly three years ago!
  5. Thanks Cory, appreciate your follow-up. But I couldn't get that working here. I tried both my BT Internet and Gmail settings (copied from my offline email program, Forte's Agent). Both gave the same error: https://www.dropbox.com/s/9el2xdxhknbcnhc/CoryVBS-Email-2.jpg?raw=1 I decided against following the detailed instructions here about fixing that error, as I don't want to risk it simply out of curiosity. https://support.microsoft.com/en-gb/help/928100/error-message-when-you-try-to-programmatically-send-an-e-mail-message And as I already have at least three email accounts I'll pass on trying SendPulse. It's not worth your spending much time on but here's a redacted version of what i tried: https://www.dropbox.com/s/it9h8dz165nkp9g/CoryVBScriptToSendEMail-Gmail-Redacted.vbs?raw=1
  6. Thanks Cory. As mentioned, I'm only using VBS as a learning exercise. And now my curiosity is piqued. The script I tried only offered me that Outlook option. Ive tried a few other scripts in vain. That one you suggested looked promising but gave the error 'The transport failed to connect to the server.' Knowing very little about VBS and almost nothing about the obscure workings of email delivery, servers and such, I was hoping to get by in copy/paste/edit mode, with little further investment! I'll put it on the back burner for now. But MEP's command works OK. Terry, East Grinstead, UK
  7. ... Write Myself a Letter. Well, an email. In retirement I no longer need an app such as MLO, like Alexis, but I do set alarms to remind me of important ToDos. Right now I'm trying to add a macro to send an email on a special scheduled date, as a backup to my regular one. I thought I'd also learn a bit more about the External Script command at the same time. The macro works fine except for the rather serious snag that the VBS script apparently insists on user input to close a dialog. It won't let me type Enter or click the mouse to close it. Not much use for an alarm I want to set days or weeks ahead! For this discussion I've shown the macro intended for immediate activation, rather than scheduled. To test just change the email address in the script. Or leave it as it is and you should get a Delivery Status Notification (Failure) email instead. The dialog that won't respond to MEP is shown below. Playing with Options and Properties brought no joy. It seems that VBS takes priority. I'm not clear why a dialog is displayed anyway as there's only one option, Outlook. (I don't use it, but my regular email app, Agent, doesn't support IMAP.) Anyone know enough about VBS to bypass that dialog please? // This macro inserts the text contents of the clipboard into the body of a message and emails it immediately (using Outlook) to my BT address. Delay: 0.1 seconds Variable Set String %tBody% from the clipboard contents Delay: 0.1 seconds External Script: VBScript Wait for Window Title: Choose Profile Text Type (Simulate Keystrokes): <ENTER> <COMMENT Value="This macro inserts the text contents of the clipboard into the body of a message and emails it immediately (using Outlook) to my BT address."/> <DELAY Flags="\x01" Time="0.1"/> <VARIABLE SET STRING Option="\x02" Destination="%tBody%" NoEmbeddedVars="FALSE"/> <DELAY Flags="\x01" Time="0.1"/> <EXTERNAL SCRIPT Language="VBScript" Dest="%tConsole%" Script="Dim ToAddress\r\nDim MessageSubject\r\nDim MessageBody\r\nDim MessageAttachment\r\n\r\nDim ol, ns, newMail\r\n\r\nToAddress = \"abc@xyz.com\" ' Change this to your own address to test\r\nMessageSubject = \"Test using VBS\"\r\nMessageBody = \"{%}tBody{%}\"\r\n\r\nSet ol = WScript.CreateObject(\"Outlook.Application\")\r\nSet ns = ol.getNamespace(\"MAPI\")\r\nns.logon \"\",\"\",true,false\r\nSet newMail = ol.CreateItem(olMailItem)\r\nnewMail.Subject = MessageSubject\r\nnewMail.Body = MessageBody & vbCrLf\r\n\r\n' validate the recipient, just in case...\r\nSet myRecipient = ns.CreateRecipient(ToAddress)\r\nmyRecipient.Resolve\r\nIf Not myRecipient.Resolved Then\r\nMsgBox \"unknown recipient\"\r\nElse\r\n newMail.Recipients.Add(myRecipient)\r\n newMail.Send\r\nEnd If\r\n\r\nSet ol = Nothing" Encoding="0"/> <WAIT FOR WINDOW TITLE Title="Choose Profile" Partial="FALSE" Wildcards="FALSE" Indefinite="TRUE" Hours="0" Minutes="0" Seconds="10"/> <TEXT TYPE Action="0" Text="<ENTER>"/> And here's the VBS script: ========================= = Dim ToAddress Dim MessageSubject Dim MessageBody Dim MessageAttachment Dim ol, ns, newMail ToAddress = "abc@xyz.com" ' Change this to your own address to test MessageSubject = "Test using VBS" MessageBody = "{%}tBody{%}" Set ol = WScript.CreateObject("Outlook.Application") Set ns = ol.getNamespace("MAPI") ns.logon "","",true,false Set newMail = ol.CreateItem(olMailItem) newMail.Subject = MessageSubject newMail.Body = MessageBody & vbCrLf ' validate the recipient, just in case... Set myRecipient = ns.CreateRecipient(ToAddress) myRecipient.Resolve If Not myRecipient.Resolved Then MsgBox "unknown recipient" Else newMail.Recipients.Add(myRecipient) newMail.Send End If Set ol = Nothing
  8. Thanks Cory, interesting idea. As per my reply to Alan, I’ve decided to tolerate the untidiness until the project is completely finished.
  9. Thanks Thanks Alan. I’ve always found that handy myself but I’ll see if unchecking makes the subject task any easier. When developing a complex macro like this one, it’s useful that when routinely saving it MEP pops up a series of warnings and tapping Y to each very quickly gets the undefined variables setup. BTW, I can’t test the following right now (posting from my iPad), but I think I’m right that I can confine my task to the main macro? IOW, even if all the many of the submacros it calls have their necessary variables defined (as well as unnecessary ones), the main macro must also have these defined too? Anyway, I’ve decided to postpone this ‘housekeeping’ until the entire project is finished!
  10. This version accepts a set of numbers, each of any length (well, under a billion!) separated by single spaces. I've just used a small test set, so add whatever input method suits, such as a prompt or imported text file etc. If you have difficulty understanding what's going on, sprinkle with Text Box Displays to show the changing values of key variables. It would be a mere three lines of script without that final 'and'! Variable Set String %tRawInput% to "1 23 456 7 8 999" // Test input Variable Modify String: Replace " " in %tRawInput% with ", " Variable Set Integer %nLenRaw% to the length of variable %tRawInput% Repeat Until %tChar% Contains "," Variable Modify String: Copy part of text in %tRawInput% starting at %nLenRaw% and 1 characters long to %tChar% Variable Modify Integer %nLenRaw%: Decrement End Repeat Variable Modify Integer: %nLenRaw% = %nLenRaw% + 2 Variable Modify String: Copy part of text in %tRawInput% starting at %nLenRaw% and 10 characters long to %tEnd% Variable Modify String: Delete part of text from %tRawInput% starting at %nLenRaw% and 10 characters long Variable Modify String %tRawInput%: Append Text ( and) Variable Modify String %tRawInput%: Append Text String Variable (%tEnd%) Text Box Display: RESULT <VARIABLE SET STRING Option="\x00" Destination="%tRawInput%" Value="1 23 456 7 8 999" NoEmbeddedVars="FALSE" _COMMENT="Test input"/> <VARIABLE MODIFY STRING Option="\x0F" Destination="%tRawInput%" ToReplace=" " ReplaceWith=", " All="TRUE" IgnoreCase="FALSE" NoEmbeddedVars="FALSE"/> <VARIABLE SET INTEGER Option="\x0D" Destination="%nLenRaw%" Text_Variable="%tRawInput%"/> <REPEAT UNTIL Variable="%tChar%" Condition="\x06" Value=","/> <VARIABLE MODIFY STRING Option="\x09" Destination="%tChar%" Variable="%tRawInput%" Start="%nLenRaw%" Count="1" NoEmbeddedVars="FALSE"/> <VARIABLE MODIFY INTEGER Option="\x08" Destination="%nLenRaw%"/> <END REPEAT/> <VARIABLE MODIFY INTEGER Option="\x00" Destination="%nLenRaw%" Value1="%nLenRaw%" Value2="2"/> <VARIABLE MODIFY STRING Option="\x09" Destination="%tEnd%" Variable="%tRawInput%" Start="%nLenRaw%" Count="10" NoEmbeddedVars="FALSE"/> <VARIABLE MODIFY STRING Option="\x0A" Destination="%tRawInput%" Start="%nLenRaw%" Count="10"/> <VARIABLE MODIFY STRING Option="\x06" Destination="%tRawInput%" Value=" and" NoEmbeddedVars="FALSE"/> <VARIABLE MODIFY STRING Option="\x07" Destination="%tRawInput%" Variable="%tEnd%" NoEmbeddedVars="FALSE"/> <TEXT BOX DISPLAY Title="RESULT" Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang1033{\\fonttbl{\\f0\\fnil Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs20 %tRawInput%\r\n\\par }\r\n" Left="Center" Top="Center" Width="278" Height="200" Monitor="0" OnTop="TRUE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>
  11. Hi Alan, Minor point: I'd place %Results% in the body of the display, not the header, which will only accommodate a small number of digits. <TEXT BOX DISPLAY Title="RESULTS" Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang1033{\\fonttbl{\\f0\\fnil Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs20 %tResults%\r\n\\par }\r\n" Left="Center" Top="Center" Width="278" Height="200" Monitor="0" OnTop="TRUE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>
  12. Thanks for testing Alan. I'm guessing that you're taking an extra step that I didn't: after step 5 are you stepping through your macro in response to successive messages that the variables mentioned don't exist, and allowing their assignment? In my case that takes a while but after dinner I'll do so and then: 1. See if the macro still runs 2. Compare the old and new variable list I'm also hazy about the many variables being used within my submacros. Directly after step 5 did you, like me, initially see on the variables page of the fresh macro only the standard 'T', 'N', etc?
  13. Thanks but no, pasting doesn't bring any variables across.
  14. I'm in the middle of a complex macro which has many sub-macros (some with sub-macros themselves). About 25 macros in total. My scripting has been a tad impatient and one consequence is that I now have about 60 variables of which maybe half are superfluous. Anyone with ideas on a fast way to isolate the redundant ones please? I could then confidently delete them from all macros in which they currently appear in the variables list. (Or maybe have a fun digression doing that with a macro.) The best I can think of is to run a macro to clear all 60 variables, run the main macro and all its sub-macros, then run a macro to display all variables, making the assumption that any which are still empty are redundant. With time zones in mind I'll probably try that shortly and report back promptly if it works OK. Friday 24 January 2020, 0955 UK GMT
  15. Hi Alan, Smart move! I’ll try that when (or possibly if) I get this Excel VBA macro finished.
  • Create New...