PDA

View Full Version : Solved: opening email with selected names and attachments



Mario62
08-25-2009, 10:49 AM
Hi,

The few hairs I have are turning white.
A question for the experts.

I have in my spreadsheet names, with as next column e-mailadresses.
I have also names of files, ant the column next to it the full path.

I can upload the names in a form. I am looking now for some code to open the default email program with the names I selected to send to and with the files in attachment.

Can someone help me please?
Thanks in advance,
Mario

p45cal
08-25-2009, 10:55 AM
can you upload a (perhaps reduced in size/data) version of what you've done so far to this site?

Mario62
08-25-2009, 12:02 PM
Thx for answering p45cal...

I am sorry - to send what I have (and not have in attachment is a bit diffucult. I am from Belgium and i have used many many dutch words in my program.

Maybe I can tell what I have done - in a very reduced way.

In excel:
Sheet("page1"):
column C: names of people
column D: their emailadress

Sheet("page2");
Column A: names of files (for example beautiful.pps)
Column B: path name (for example D:\powerpoint\beautiful.pps)
Column C: seize
Column D: date
etc...


In VBA
Form 1 - to retrieve all file information by opening dialogwindow and choosing folder

Form 2 - with listbox were I can see all names of files and their data.
When I doubleclick on the listbox opens a new form => form3.

Form 3 - There I have again all the information of one file. I can change this information. I can also see who has already received that file from me. There are multiple names.

Form 4 - works different. In this form I can choose one person out of many. One listbox tells me what only that person has received (mutiple files) - another listbox tells me what he did not receive.

What I want?

In form 3 - there I have multiple names. When I mark multiple names (by checkbox of button) then i want their name in my emailprogram as person to send to. The name of the one file do i want to be in attachment.

In form 4. When I click on the listbox with the names that the one person did not receive, then is the name added to a third listbox. Multiple names of files can be added. When I have finished my choise then I want to push a button - the emailprogram opens - the one person is in "send to" and the multiple files are in attachment...

I hope this makes something clear

Thanks,
Mario

p45cal
08-25-2009, 02:40 PM
There are so many unknowns. Just a few: how many files per email address?, how many columns in the listboxes have you used? What event's have you chosen to exploit in the various userforms?...and so on..from what you say the programme seems complex - how far have you got?

I have done this sort of thing before for a company I worked for, and there are lots of possibilities and routes that could be taken, just which route depends on a multiplicity of things. While I am prepared to help, I'm not going to code for the whole of the functionality you want, after all, VBAExpress is not a free code-writing service.

Help us to help you.

ps. How did you know I don't speak Dutch?

Mario62
08-27-2009, 04:23 AM
ty

I have found...




Option Explicit

'Used for creating an e-mail
Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" _
(ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long


Private Sub CommandButton1_Click()

'email
Dim sMailTo As String, _
sSubject As String, _
sBody As String, _
stext As String, _
MyCell As Range, _
PrevRow As Long, _
sSend As String, _
sFile1 As String


' get info from sheet
Range("c2").Select
sSend = Selection
Range("a1").Select
sFile1 = Selection

'email ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

sMailTo = sSend
sSubject = sFile1
sBody = ""
PrevRow = Selection.Row

stext = "mailto:" & sMailTo & "?Subject=" & sSubject

If Len(stext) > 2000 Then
MsgBox "That message is too long", vbCritical, "Error"
Else
'Send e-mail
Call ShellExecute(0&, "open", stext, vbNullString, vbNullString, -1)
End If

'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++

End Sub



I am still looking for the code to attach the file itself.
I have the full name and path of the file in the spreadsheet.

Any idea's please?
Mario

p45cal
08-27-2009, 04:23 PM
I've looked at the attachment but can't see any forms with listboxes in them so I can't begin to tackle the likes of
In form 3 - there I have multiple names. When I mark multiple names (by checkbox of button) then i want their name in my emailprogram as person to send to. The name of the one file do i want to be in attachment.

In form 4. When I click on the listbox with the names that the one person did not receive, then is the name added to a third listbox. Multiple names of files can be added. When I have finished my choise then I want to push a button - the emailprogram opens - the one person is in "send to" and the multiple files are in attachment...
however I am looking at the event code for the only control (a button) on the one userform that is in the workbook you attached but won't have an answer before tomorrow (Fri 28 Aug 2009) because I'm knackered just now and going to bed!

What email application are you using?

Mario62
08-27-2009, 10:56 PM
Ok - got the message :yes


Here in attachment were I am working on...

thx,
Mario

p45cal
08-28-2009, 04:17 AM
What email application are you using?more to the point, What email application(s?) will the users use?

Mario62
08-28-2009, 04:23 AM
Incredimail and Windows Mail...

Mario

p45cal
08-28-2009, 12:19 PM
This is an interim response since I'm aware of time passing. I have searched a lot and I doubt very much that ShellExecute will allow you to send attachments with either Windows Mail or Incredimail (I don't have either). If you'd responded 'Outlook', this would be a doddle.
However, I do think you'll be able to do it with CDO:Sub CDO_Mail_Small_Text() 'orig should work as is with windows mail account
Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
strbody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"

With iMsg
Set .Configuration = iConf
.To = "pdaulton@smith.net"
.CC = ""
.BCC = ""
.From = """Ron"" <ron@something.nl>"
.Subject = "Important message"
.TextBody = strbody
'.AddAttachment ("file://C:\ASLog.txt")
'.AddAttachment ("C:\ASLog.txt") 'this worked too, I got 2 attachments
.Send
End With
End Sub
This code is adapted from http://www.rondebruin.nl/cdo.htm where there are further instructions should it fail.
I still need to incorporate this into your workbook and its userforms.

A downside to using CDO is that there'll be no record of emails being sent in any Sent Mail folder anywhere, since it submits the email directly to a server. Could this be a problem?

Is it just you who will be using this workbook, or will it be distributed to a significant number (say more than 2) others? I ask to see whether it's worth coding to handle any mail system.

Regarding your workbook, my initial thoughts are that the listbox should contain several columns (most not visible) per email address, that Multiselect would be enabled for that listbox, and the code would run through that listbox, checking for the selected property of each row and pick up the rest of the information from the other columns (attachment filename etc.).

Other snippets/sites that may be of use;
Code at the bottom here:http://www.ozgrid.com/forum/showthread.php?t=95504
Once the email system has been determined I was exploring exposing their objects to vba (if available) by createobject using ProgID (programmatic identifiers - (CSLID?) here:http://www.informit.com/articles/article.aspx?p=1187429&seqNum=4 (http://www.informit.com/articles/article.aspx?p=1187429&seqNum=4)
(which incidentally tells me that Windows Mail doesn't expose anything!)

I've started exploring MAPI here:http://www.thescarms.com/vbasic/mapiemail.aspx
There are also links at the bottom of this page which I haven't explored yet:http://www.sunny-beach.net/manual/591.htm

Mario62
08-29-2009, 01:41 AM
Many thanks p45val. I surely appreciate the efforts and time you are spending on my problem!

I going to try all the things you have written. It will be a puzzle but maybe is the answer in it.
For the moment it are me and another - but I think it is best to make it for every email prgramm

Thanks again,
Mario

p45cal
08-29-2009, 03:36 AM
Is it just you who will be using this workbook, or will it be distributed to a significant number (say more than 2) others? I ask to see whether it's worth coding to handle any mail system.

Mario62
08-29-2009, 03:42 AM
Sorry, P45cal if my explanation was not so good.


For the moment it are me and another - but I think it is best to make it for every email prgramm
So, for the moment I use Incredimail. My friend is using Windows Mail but I can imagine that also other friends will like to use it.

Mario

p45cal
08-31-2009, 02:02 AM
What progress?

Mario62
08-31-2009, 11:25 PM
no progress...

I have found on other websites that in using MailTo it is not possible to add an attachement. I have found how to place the emailadress in BCC and the names of the files in subject
...



stext = "mailto:" & sMailTo & "?Subject=" & sSubject & "&attach=" & sbestand & "&bcc=" & sBCC


Mario

p45cal
09-01-2009, 12:02 AM
That's using ShellExecute. I said as much in post #10
I also said: "I do think you'll be able to do it with CDO"
Did CDO work?
(if it didn't, don't just say 'No' - say where it fell over)

Mario62
09-03-2009, 03:17 AM
I'm sorry... i do not know how to work with CDO. I tried to copy, paste, adapt several things but it didn't wend well. Must be me...

Thanks, I have finished it like i told before.
Mario

p45cal
09-03-2009, 05:41 AM
Thanks, I have finished it like i told before.
Mario Where that?

Mario62
09-14-2009, 02:26 AM
stext = "mailto:" & sMailTo & "?Subject=" & sSubject & "&attach=" & sbestand & "&bcc=" & sBCC
No solution but a step in between...

Mario