2 Attachment(s)
Here’s my 'micro': ---- :)
Hi reda2006
Welcome to the excelfox Forum.
:)
I have a good basic VBA Knowledge and automate a lot of my daily stuff with it.
But I have never sent an Email using VBA.
But I think I can manage a micro response for you:
Here’s my 'micro':
Do a simple Internet search with terms like
__ send email using vba
or better still
__ send email using vba youtube
You will see that you are flooded with a lot of tutorials showing you how to do it.
I just took a quick look and I think some of them are very easy to follow.
I like this guy, he is very slow and thorough
https://www.youtube.com/watch?v=pFl7W8d7d4M
But best is to go through a few of the vidios.
What I would ( will probably later**** ) do is download them all, make a play list and let it run in the background for a few days while I do something else ..
They will tell you how to send a single email I expect. I think I noticed some showing how to add an attachment as well.
It is very simple basic VBA stuff to automate something to be done a lot of times once you have figured out how to do it for the single case.
It is also very simple VBA stuff to read the email addresses and the file names from the lists as you described them
You will need to know a bit of basic VBA stuff, but you really should do that even if you are doing it as a one off.
If you do anything at all with Excel then you will find that some things are incredibly easier with a bit of simple coding.
Most Excel stuff itself is just a lot VBA coding already written. So VBA is crying out to be used to tailor things more exactly and efficiently to your requirements.
So the best thing to do is to:
_ get a bit clued up on how to do it.
_ while you are doing that , make up a mock up file / files with desensitised dummy data. Keep the data to a minimum but pick it carefully to test out all scenarios.
Try and get that working. Usually a code working on a small amount of data will work on a much larger amount with little or no modification
_..____________
So, there you go - That was a fairly micro response, at least very micro by my standards. ;)
****Having said that I might be interested in trying to automate sending some files by E-Mail myself sometime in the next week or so. Let us know how you get on and might be able to help later.
But if it is urgent, then as snb suggested, a Forum probably isn’t the best place to ask. A Forum is more of a “self help group” really. We all have a lot of other stuff to do, and rarely anyone responds to an “Urgent” appeal here.
Anyway I will take a look at this in few days for my own case, so might post back , and will certainly take a look to see if you have posted anything
Good luck
Alan
_._____
EDIT: If you look at the bottom of this Thread you will often see suggestions for similar Threads:
VBASendEMailThreadSuggestions.JPG : https://imgur.com/qt1x7Mf
Attachment 1964
VBASendEMailThreadSuggestions2.JPG : https://imgur.com/X5zLEox
Attachment 1965
It might be worth taking a look at those as well
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
I Don't think I have Outlook, .. currently installed :(
Thanks snb.
I don't seem to have Outlook. I mostly have Excel2007 and 2010 currently. All Student and Home editions
( But I have collected some Excel 2003 discs as I was thinking of going "retro" and using Excel 2003.
Apparently it should be in there. It doesn't tell me on the disc what is in them and those discs that I have used already I custom installed just Excel. I don't remember if I saw Outlook listed. But apparently it should be there:
http://www.itprotoday.com/office-365...tions-compared
Maybe I will take another look if I install Outlook sometime )
Alan
3 Attachment(s)
Hmm it is but isn't with Office 2010, maybe an update thing is ( was )
Quote:
Originally Posted by
snb
If you ... install Excel 2010 .... also ....l Outlook.
Correct . ( . Edit - actually not.. or maybe later sort of ) ! My mistake.
EDit. If I can Install Excel 2010 , I cannot install Outlook , not currently with the Office 2010 instillation disks that I have. ( I have 3 of them, each has 3 valid licences, approximately half the licences are in use by me or my wife )
This was my current situatuon and mistakenly I did not realise that I do indeed habe Outlook (and Access) in my Office2010 instillations.
It is not in my Offfice2007_..
OutlookNotInExcel2007.JPG : https://imgur.com/jSp2eEb
Attachment 1967
-.. so I assumed it wasn’t in my Offfice2010 either.
But I now see that it is currently in my Office2010
snbWasRightOutlookIsInMyExcel2010.JPG : https://imgur.com/vuRcrlt
Attachment 1968
Strange it is not in Office2007 .. or maybe not strange .. maybe there is an explanation**
Thanks for pointing that out and adding the other link
This Outlook business looks like a big subject.. maybe it will be a bit longer until I post back here with my attempt at Email with VBA :)
_.___________
Edit: I am confused.. On my Disk boxes it says I don't have it, ACCESS neither
https://imgur.com/OblEx82 : Office 2010
( there are 3x3=9 Office 2010 licences there in those 3 disks
I used those disks some time ago ( approx 2 years ago on avarage , possibly sooner in some cases ) . I do not think I had the option for Outlook or Access when I instilieret.
( I also did 3 instillations a long time ago from this 3 licence Office 2007 disk
( https://imgur.com/PE8WP6t : Office 2007 on the right ) )
So... I do not think I had the option for Outlook or Access when I instilieret. But clearly I do. have it when I now look,,, How very Strange
Never mind :)
_.. I need to install Excel 2010 on a new computer, so I will see what happens later
So I did a install...
I could install Excel 2010, but it seemed to indicate that ACCESS and OUTLOOK were not on the disc...
OutlookAndAccessNotThereWhenInstalledFromDisc.JPG : https://imgur.com/R8nftch
This bit took a long time
UpdatesDuringInstalaltionExcel2010fromDisk.JPG : https://imgur.com/qvV2F1J
-. and in the end I have no Outlook or Access
NoOutlookOrACCESSAfterNewInstal.JPG : https://imgur.com/A2JRe0F
Attachment 1966
Maybe it came before ( and might come later ) on an update….
Possibly this might cause it to come later…
IClickedForUpdatesAfterOffice2010FromDiscInstillie ren.JPG : https://imgur.com/UqTLIeB
OfficeOpenXML FormatWTFIsLat.JPG : https://imgur.com/EVx7CKo
So it seems that I could not install Outlook when I could instal Excel 2010... But maybe it will come later...
** maybe Office 2010 is or was supported for updating updates to update the Office to an updated version that does have the extra stuff
I have not found any infomation yet to clear up the strange fact thatas
_ I do not seem to have Outlook ( or Access) on my Office 2010 insillation disks,
and yet
_ appear now to have Outlook and Access installed on those computers on which I used thoes disks to install Office 2010 a few years ago.
Yesterday I used one of those disks to install Office 2010, and as i expected I have not finally got Outlook or Access installed. I beleive this was the case originally ( but I am not 100% sure ) when I used the disks a few years ago to install the Office which now appears to have the extra Outlook and Access installed.
Never mind, Strange goings on in the internet...
EDIT: Later after
I know what happened.
The Elves came in the night and installed Outlook and Access on my computers a while back
How nice of them
I will add this to my list of videios to watch: https://www.youtube.com/watch?v=lPZ9_Uqu10c
TheElvesAndTheShoemakerAndEmailUsingVBA.JPG https://imgur.com/2TFZmyR
Thankyou little Elves. Please could you finish building my house for me so that I can sit on my computer all year instead, and not just in Winter. Thankyou in advance
Alan and Petra ( aka Jackie )
]VBA to automate Send and Automatically Sending of E-Mails and Excel File Workbooks
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
