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
Reply With Quote
Bookmarks