Urgent support needed. Multiple emails, multiple Excel workbooks, at once.

[DocAElstein]

VBA to automate Send and Automatically Sending of E-Mails and Excel File Workbooks.

Here’s my macros … or rather macros on it.
It seems to be potentially easy to do this and there are massive amounts of stuff on it in the internet.. at least on the easy bits… You are totally flooded with information on it if you search the internet. There is too much of it probably and a lot I looked at was very bad, so I have tried to narrow it down a bit and I reference what appeared to be the better.
Also there is often a catch: The very simple ways require you to have already done a lot of very complicated stuff before… or you need to be very competent in adjusting many computer settings and options

The background theory is less well documented.
So I tried to understand a little of the background theory and made myself some notes. I share them in this post, and in the next 2 posts I get on with doing something useful, and describe everything necessary to get a fully working example
So this post is just background info. I like to know as much as possible about the codes I use.

Introduction. Background Theory: Computer Obfuscation
The main background technology implemented is Computer Obfuscation:
If you try to understand exactly what is behind all the stuff you might end up using to send an Email with VBA, then it is impossible: – you get sent around in circles of obfuscating computer terms which often, in a very indirect way ,reference each other in a very long complicated loop - The result of this is that by the time you come back where you started you forget you were there: Unknowingly you go around in circles and after a while start repeating parrot fashion bits of what you have read:- fool yourself and others into thinking you know what you are talking about.
I am not criticising: I am inheritably using Polymorphism as I speak so I may be an Object Linked Oriented Hierarchical structure. I don’t know either.
Often a lot of the stuff is wrapped up in interfaces at different levels in the computer workings. The word Interface is a term used to intimidate. It just means the bits you can get at and possibly use to do something that might be useful to someone, and what is likely dangerous.

COM OLE Bollocks
Somewhere deep down in software workings of a computer is some stuff, most of which Microsoft have something to do with which way back in the early 90’s tried to set some standards to allow different software to be embedded in each other. Important to the obfuscation was the use of the word “object”. The use of object has no particular meaning in this stuff, but does in others , so that helps to confuse. This is an important feature of the Obfuscation technology being used. The deep down fundamental stuff , which includes stuff been there the longest goes by the name of Component Object Model. Stuff which is often, but not always, later stuff, or at a slightly higher level of the computer workings, or slightly more to a specific application ( an actual running “runtime” usage / at an instance in time , “instance of” ) orientated goes to the name of Object Linking and Embedding

ActiveX ActiveX Controls Security problems probably on purpose and the attempts at curing that break stuff probably on purpose as well .
It seems most of the stuff was attempted to be put in a more user friendly set of software going under the name of Active X. This was then mainly to do with interaction of different software electronically, and inevitably that seemed to mostly end up making it possible to link stuff up over the internet. An offshoot of this is to use the programs in things that should not necessarily be directly concerned with the Internet which loosely go under the name of Active X controls. It seems to have become a major feature of this technology to allow smart computer people to get at everything everywhere. This may have been an accident or was done on purpose to allow later manipulation of everything: I don’t think anyone knows for sure. Things later going by the name often of security updates to prevent some using this ability to get at everything, tend to influence all sort of things which for an end user often means that something does not work anymore…

API: Application Programming/Programmer Interface
Very loosely this is the interfaces that are fairly easy to use from a fairly higher level from within a higher level programming language. In other words this allows you to get at and use some of the stuff to do with the COM OLE Bollocks from within a programming language such as VBA.
You could even call Active X an API, but it isn’t usually for no particular reason.
In every day casual speaking API is often referring loosely to do with using certain shipped with Windows software in Folders often having the extension dll. This extension , or rather the dll stands for direct link libraries. These are special sort of executable files of functions shared by many other ( Windows based usually ) software’s.
Folders mostly but not always are in some way referenced using dll, either as noted with the extension or maybe referred to as dll Files or dll API files.

dll
It easier to understand this dll technology probably by considering what could be considered a less advanced alternative way of using such shared libraries, that being “static linked library” technology. This latter case would somehow at compile time copy all required external libraries. So for many finally complied programs a lot of duplication of things might take place , making inefficient use of computer memory in this latter case.
The dll alternative is some sort of two step approach – a term called “exporting” somehow gives an indication of what libraries are usable to a specific application, and a term called “importeding” allows the use of the functions in a library at run time. Somehow it is all in all a more efficient way to work in a computer, the files are “executable” but exactly how no one remembers anymore. The lid is left on, it’s called an “interface” and hoped for the best.

Using one or more dlls in a VBA code ###
It is possible to get at a specific single or a few similar dll libraries.. To do this in VBA code an initial code line something similar to a Dim code line is done. This is a Declare type code line at the top of a code module. After that an API Function is used in a code similar to a normal VBA Function
( For example http://www.excelfox.com/forum/showth...tion#post10465 ).

“Libraries available to VBA”
The word Library is used sometimes like Object a bit loosely to help contribute to the Obfuscation technology used in most of the things being discussed here.
The word Library is used often when use of dll stuff is bundled up into something like a collection of Functions and other stuff that include things that use one or more of similar dlls or use dlls that do similar stuff. This is organised something like a Visual Basic Class Object, ( An example of such a Class Object is a VBA Class Object called Worksheet ) with the available Methods Properties Constants and Functions etc which can be made available to any VBA code. The collection goes by the name of a Libraray. Specifically it is one of those Libraries that you need to put a check against in the usual way of … “……-- VB Editor -- Tools ---- references --- scroll down and check the appropriate library from the massive list of those available….”……
Often instead of calling these things Libraries they are referred to themselves as an API. So one can see that the term is used in a very loose Obfuscation type way.

So Finally, after all that 2 or possibly more ways to automate Send and Automatically Sending of E-Mails come out, which are similar
One way to
VBA to automate Send and Automatically Sending of E-Mails.
_1 ) Way 1) Use the CDO (Collaboration Data Objects ) object library available in VBA.
_Q) Ask a computer expert or two ( I did ) what is that CDO
_A)
Collaboration Data Objects (CDO), previously known as OLE Messaging or Active Messaging, is an API included with Microsoft Windows
or
CDO is an object library that exposes the interfaces of the Messaging Application Programming Interface (MAPI)
_Q) What is the MAPI
_A)
Messaging Application Programming Interface (MAPI) is a messaging architecture and a Component Object Model based API for Microsoft Windows.
That is probably what I would say if someone asked me. So none of us know. We just go around in circles when we try to explain and think we said something useful.

But approximately what it means is that you are trying to get well down the level of the computer to actually do things over the internet like simple messaging and hopefully are hopefully then not too well linked to the more all encompassing and therefore dangerous Active X stuff which sort of does in a way grow out like a malignant cancer from about the same place down there deep down in the computer.
But really no one knows.

If I use the CDO in a VBA code then I can say that I am using the Excel API. ( I did use CDO in a VBA code, and I did just that I am using the Excel API – so there is another proof )

If I add a few other bits along the way and disguise what I am doing a bit than I could say another application, all together: So say with me like:
“Another Application” … This leads on to the second way:

_2 ) Way 2)
Office Software.. one of ‘em
Microsoft Outlook ( an Active X Out Of Control )
Microsoft Outlook is part of the General Microsoft Office Software Stuff. Which versions of Office it is available with is not clear yet ( http://www.excelfox.com/forum/showth...0512#post10512 )
Mostly it seems to be available in Office, or not difficult to get. I have it in Excel 2003, don’t have it in Excel 2007, and have to rely on the ****Elves to put it on my Excel 2010.
The mystery of the “Elves and the Shoemaker is revealed”. It was an Active X Control that went wrong and Outlook 2010 slipped through and got installed on my Office 2010
In layman’s terms, Microsoft Outlook is a personal information manager. In this day and age it means it has an awful lot to do with the Internet, especially E-Mails, E-Mailing, Email organising etc..
In reality it may not have been made by Microsoft.
Microsoft Outlook was probably made using Word and Excel and the CDO by an Elf, aka an Active X OutOfControl. The same Elf or Elves installed Outlook on my Computers.
Microsoft Outlook: what is that ( using manually )
You would normally get the software to run on its own ( visible as it were ) in a similar way to which you might get Word or Excel to start
For example:
Find it single click on it:
FindOutlook Start AllProgrammes Microsoft MicrosoftOutlook.JPG : https://imgur.com/LaGs6HA
FindOutlook Start TypeInSearchBox Outlook.JPG : https://imgur.com/IbFOSHz
Make a Desktop icon from a Copy/ paste and double click on it :
MicrosoftOutlook Make a desktop Icon to double click on.JPG : https://imgur.com/ZNNPmOI
The first time you try to open it with a click or two, a set up starts.
Outlook2003Start.JPG https://imgur.com/tSQDoTe
The main use of the Outlook software is “to do Email stuff”, so usually you will have at least one Email account “registered in it” You can do this at the set up or later.
The internet is full of stuff on this, but there is no clear explanation of what it is or what it should do or how you do anything with it.
I had a go , and here are some notes on my failed attempts:
http://www.excelfox.com/forum/showth...0513#post10513

After many hours I was still none the wiser, but it is worth doing all that what I did ( whatever it was ) , anyway as you may need some of that information later in one or more of the ways to send an Email using VBA.

As far as we are concerned ( here at least ) , we can move on if we consider just this.
Excel has Excel VBA. It can be used to automate Excel and related stuff
Word has Word VBA. It can be used to automate Word and related stuff
Outlook has Outlook VBA. It can be used to automate Outlook and related stuff.
In Excel after some initial “Object Library” making within the code at the start, you can make use of other applications VBA, such as Word or Outlook, from within an Excel VBA code.
We want to do Outlook related stuff ( Email related stuff ) in a VBA code.
As long as we “have Outlook”, and correctly do the initial “Object Library” making within the code at the start then we can use the various Object, Methods, properties etc, which it has to offer that might be useful to us.
( Later we see that this is actually not quite the case as there is a big catch )

_3 ) Way 3) Using MAPI “calls”
Using xxAPI calls is computer slang for doing the dll codes in a VBA code via the Declare then use as a normal Function way in VBA code###
I am guessing it should be possible to make a few MAPI Declare statements at the top of a code module and then write a VBA code to do some simple Email sending. I have not seen this done yet. I might have a go.

_4 ) Way 4) RoutingSlip Object
This is an object available only up to and including Office 2003. Every Workbook( up to and including Office 2003) has such an object. Primarily this is intended to allow simple sending of a Workbook.

_5 ) Way 5) sendmail Object
This is a method which allow simple sending of a Workbook



Ref:
http://www.snb-vba.eu/VBA_Excelgegevens_mailen_en.html
https://powerspreadsheets.com/send-email-excel-vba/
http://www.ozgrid.com/VBA/send-email.htm
https://stackoverflow.com/questions/...books-with-vba

[DocAElstein]

_3 ) Way 3) Using API “calls”
Using xxAPI calls is computer slang for doing the dll codes in a VBA code via the Declare then use as a normal Function way in VBA code

I almost found a way to do this.. it took a bit of unconventional thinking before I hit on an idea that almost worked..

Execute stuff
Some time ago computer screens were mostly black.
The typical syntax to get something to run was just typing in something similar to the file path , or something that might be recognised by the computer as similar to, the path to the software that you wanted to run. At least that is the way I remember doing it when I was little, at least in the time in between playing with my plonker.
Back in those days you talked about doing stuff. ( Sometimes the word Command was used for what was written or shouted as the message to do something). If you wanted to be a clever poof then you used a word like Execute instead. Computers were clever stuff back then so instead of doing programs they were “Executed”. I executed a lot of early computer stuff- I killed, trashed and broke them

Some of the kids of about that time spent a lot of their time in Penny Arcades playing and playing on older computer. They went on to come up with things like Windows. But often the basic ideas associated with executing in terms of doing and braking stuff are still there and it goes back to when “activating a link” in electronic messaging often exposed an interface giving the possibility of getting some thing done.
Double clicking on a file, or putting a path or URL link in a Windows explorer bar or browser bar often does something similar. The possibility of doing something as an application programmer closely related to these operations is made available in Libraries going by names similar to Windows Shell.
Things of the “Execute” or “Command” nature often involve giving a long string reference path to expose…

As noted previously, .. from Microsoft documentation… Visual Basic provides polymorphism through multiple ActiveX interfaces. In the Component Object Model (COM) that forms the infrastructure of the ActiveX specification, multiple interfaces allow systems of software components to evolve and break existing code. ……. In this sense interface is a set of related properties and methods. Much of the ActiveX specification is concerned with implementing standard interfaces to obtain system services or to provide malfunctionality to other programs.

So delving deep into the innards of computing, I need to look for something, probably in a dll library that has been there a long time doing a lot of damage through the exposing of interfaces looking through long string references paths: There must be one there that the more upper level stuff has used. A good tip seems to be to look for API dll Functions that have been modified a few years back.
http://www.jkp-ads.com/articles/apideclarations.asp
This seems to be what “PtsSafe” is all about. It is almost impossible to find any clear documentation on what that is. But it appears to be an attempt to pull back a bit the Monster of ActiveX roaming uncontrolled in computers..

Sure enough , a bit of searching in documentation they forgot to remove from the internet , or old books, reveals this API function.. which a typical one which need a bit of extra “If VBA7 Then Else” stuff which is done at pre compile to see if your computer is newer and so has the PtrSafe stuff

Option Explicit
#If Not VBA7 Then
Private  Declare  Function DoExecCmd Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal Operation As String, ByVal Filename As String, Optional ByVal Parameters As String, Optional ByVal Directory As String, Optional ByVal WindowStyle As Long = vbMinimizedFocus) As Long
#Else
Private  PtrSafe Declare  Function DoExecCmd Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal Operation As String, ByVal Filename As String, Optional ByVal Parameters As String, Optional ByVal Directory As String, Optional ByVal WindowStyle As Long = vbMinimizedFocus) As Long
#End If
Sub DoExecCmd()
Dim lSuccess As Long
 Let lSuccess = DoExecCmd(0, "Open", "mailto: Doc.AElstein@t-online.de")
End Sub

This code brought up some Windows Email thingy.
I guess , just like Outlook. It is there… but god knows how you use it. I tried to send something… because I had never used it before it sent me off on an attempt to set something up:
I had a go:

WindowsEMailSetUp 2 .JPG : https://imgur.com/S7U9cB1
WindowsEMailSetUp 3 .JPG : https://imgur.com/7qGOznM
WindowsEMailSetUp 4 .JPG : https://imgur.com/ww5KZAe
WindowsEMailSetUp 5 .JPG : https://imgur.com/5nkVFB0

It almost worked:

I got an error
WindowsEMailSetUpThenFail .JPG : https://imgur.com/0goEMbZ

Fehler bei der Verbindung zum Server. Betreff 'test', Konto: 'IMAP4', Server: 'secureimap.t-online.de', Protokoll: SMTP, Port: 25, Secure (SSL): Nein, Socketfehler: 10061, Fehlernummer: 0x800CCC0E


Error connecting to the server. Subject 'test', Account: 'IMAP4', Server: 'secureimap.t-online.de', Protocol: SMTP, Port: 25, Secure (SSL): No, Socket Error: 10061, Error Number: 0x800CCC0E

So I guess it is the same story as with Outlook: You need a hell of a lot of in depth computer knowledge that probably no one ever remembers anymore to set stuff up. Most people using this stuff have this set up already and probably don’t know half the time what it is they are actually doing…


Daniel Appleman “Visual Basic Programmer's Guide to the Win32 API”
https://msdn.microsoft.com/de-de/en-.../bb979032.aspx





_4 ) Way 4) RoutingSlip Object
This is an object available only up to and including Office 2003. Every Workbook( up to and including Office 2003) has such an object. Primarily this is intended to allow simple sending of a Workbook.

Any published codes which work bring up various pop ups asking for information. I have no idea what they all mean and I can’t find any documentation or Blogs to explain this or show any working example.
I suspect this way might also require some interaction with some existing Email program.
So I do not see it as a realistic option.


http://www.snb-vba.eu/VBA_Excelgegev..._en.html#L_2.2
https://msdn.microsoft.com/en-us/lib...ffice.11).aspx
https://msdn.microsoft.com/en-us/lib...ffice.11).aspx






_5 ) Way 5) sendmail Object
This is a method which allow simple sending of a Workbook

This is one single code line. The coding is not the issue: …. As with ways 2, 3 ,and 4 , it attempts to call up an existing Email program. It requires all sorts of complicated setting up specific for each computer and Email program.
It is therefore not a practical solution in my opinion.




https://msdn.microsoft.com/en-us/vba...l-method-excel




Final conclusions to VBA to automate Send and Automatically Sending of E-Mails and Excel File Workbooks.

There appears to be only one realistic way to do this: Way 1 : Use the CDO (Collaboration Data Objects ) object library available in VBA
I was able to apply this way very well.
It allows you to send full formatted messages and attach files completely from VBA.
The code can be run from VBA Word or VBA Excel.
This allows for an easy customisation to suit a particular requirement involving automating of sending E Mails with attachments.

There do not appear to be any other ways to do this. The other published ways and codes which I investigated are very reliant on other program settings: They are dependant on existing Email programs and require extensive setting up on as particular computer system. They do not really use VBA to automate Sending Emails. They are simple additions or “add -ons” or “pseudo Buttons” to set off an existing Email program.
I was very surprised by this… and so where almost half the authors of published codes that I contacted. I likened their sharing of their codes as similar to giving a train ticket valid from Berlin to Munich to an Aborigine wanting to go to Munich: Very useful to him… as long as someone else takes him and organises putting him on a train in Berlin.
Ways 2, 3 ,and 4 are minor additions to something else and are no solution alone to Automatically Sending of E-Mails and attachments.











Ref:
http://www.snb-vba.eu/VBA_Excelgegevens_mailen_en.html
https://powerspreadsheets.com/send-email-excel-vba/
http://www.ozgrid.com/VBA/send-email.htm
https://stackoverflow.com/questions/...books-with-vba
http://www.databison.com/3-nifty-way...l-using-excel/