Excel VBA to email attachments 2

[Rexxx]

I am having some trouble coding VBA to email an Excel attachment.

My vba sub copies cells to an excel form then emails the form as an excel file which is working swell but when the email with attachment opens it will not allow me to paste my Excel email recipients address (grabbed from an Excel cell and pasted in clipboard) into the To field. I have a whole Excel column full of addresses that my vba is coded to save to the clipboard each time to paste in the To field. The macro always stops with the email and attachment open and To field blank.

There must be another way to do this. Help!

 

[kylua]

Don't use the send to function to email the file as an attachment. There is plenty of examples on this board of doing all the mailing entirely behind the scenes thru outlook and notes. So you can actually mail the file to all recipients with one click of a button.
The code you need depends on the mail program that you have.
(and i know nothing about outlook)
As to why you're losing data out the clipboard, excel has a funny way of dealing with the clipboard. No other program I know allows you to clear the clipboard by pressing escape.

 

[Rexxx]

I'm not emailing one file to a list of recipients but rather one file that will change each time to one recipient that will change each time. The only way I can think of doing what you suggest is to save each form as a unique file name then spend a bunch of more time to program Outlook to email each one of these unique files to a unique address which would involve matching file with recipient. The coding to do that would seems cumbersome.

Hmmm..

 

[kylua]

As I said, not an expert on outlook.
But in notes, you simply save the file to a temp folder, open a Notessession object, set the email address, subject line and body, attach the attachment and send.

The subject line may well be the same every time as will the body so you all you need to change everytime is the address.

So effectively bearing in mind I haven't got the eg in front of me and this is just to demo so syntax is well out:

Application.DisplayAlerts = False'stops it warning you you're overwriting
activeworkbook.saveas "C:\Temp\temp.xls"
mailadd = range("A1&quot

Set s = CreateObject("Notes.Notessession&quot

set db = s.notesdatabase(name, path) 'leave it blank; ("","&quot, it'll use the default
set doc = db.newnotesmail
doc.subject = "This is the mail you wanted"
doc.body = "The attached is todays stats on the production of Widgetts by Joe Bloggs Inc"
doc.attachment("C:\Temp\temp.xls&quot
doc.mailto = mailadd
doc.send
close s

The above WILL not run. I'm only just getting into notes-VBA so I can't rattle off the syntax. And as you're not looking to use notes, not much point looking it all up again. (I've left the proper code at work).
But it is just about as simple as that.

I made it work on notes by copying and pasting the code and just changing the parameters. I use the same basic code to stick a button on any sheet that needs emailing. It takes 1/4 of an hour to set up a spreadsheet so that it auto mails itself somewhere, and most of that time is thinking up the subject line and email body.

It's got to be that simple in outlook as well, ( I am prepared to be corrected, it is Microsoft)

PS I'm not an expert in Notes-VBA either, but, with time, I can get there.

 

[Rexxx]

This suggestion is so much more work! I will literally have a thousand different files to matchup with a thousand different email addresses. Think about that.

My Excel vba creates a unique form to be mailed to one unique address each and every time. All that must be done is to grab the address that is in the same Excel row as all the information going to the form, place in the email To field and send it. Then go onto the next row and new form. Wow, I simply cannot believe that there isn't some way to code the vb to simply grab an address contained in one cell then fill the To field.

You maybe right but it just seems odd to me.

 

[kylua]

The above mailing code can be called with each form and each email address.
Example of how it would be implemented:

sub create_forms()
for n = 1 to 1000
createform 'call a function that creates your form or replace with relevant code
workbook(tempform).saveas "C:\temp\tempform.xls" 'or however you are saving your form
mailadd = (get it from the end of the row for each form)
call emailer(mailadd)
next n
end sub


sub emailer(mailadd)
Set s = CreateObject("Notes.Notessession&quot
set db = s.notesdatabase(name, path) 'leave it blank; ("","&quot, it'll use the default
set doc = db.newnotesmail
doc.subject = "This is the mail you wanted"
doc.body = "The attached is todays stats on the production of Widgetts by Joe Bloggs Inc"
doc.attachment("C:\Temp\tempform.xls&quot
doc.mailto = mailadd
doc.send
close s
end sub

And robert's your father's brother. That'll work for 10, 1000 or 100,000 forms mailed out. You need to replace the notes sending code, (and I'll remind readers that the above code is demo only, it won't compile as I don't have the actual code on this comp), with outlook code, but that'll be elsewhere on this board. (Should be). I'm merely attempting to demonstrate an avenue of approach.

Alternatively, try this thread thread707-97538

I will admit that I am biased, I got the handle on mailing thru notes and now I use that approach at every opp.

 

[Rexxx]

Okay Kylua, this is what I came up with. In this code I'm working between two different worksheets since the one is getting emailed (actually saved to draft to release later):

Public Sub Test()
Dim olapp As Object
Dim oitem As Object
Dim Allattachments As Object
Dim currentAttachment As Object
Dim strpath As String
Dim strRecipient as string

'Fetch recipient info
strRecipient = Workbooks"BudgetExample.xls&quot.ActiveSheet.Range("A11&quot

' Do all the copying (got this from bruintjes code, faster solution than my first one)
Workbooks("2003budgetinfo.xls&quot.ActiveSheet.Range("C3&quot = strRecipient
Workbooks("2003budgetinfo.xls&quot.ActiveSheet.Range("D5&quot = Workbooks("BudgetExample.xls&quot.ActiveSheet.Range("B11&quot
Workbooks("2003budgetinfo.xls&quot.ActiveSheet.Range("D6&quot = Workbooks("BudgetExample.xls&quot.ActiveSheet.Range("C11&quot
Workbooks("2003budgetinfo.xls&quot.ActiveSheet.Range("D8&quot = Workbooks("BudgetExample.xls&quot.ActiveSheet.Range("D11&quot
Workbooks("2003budgetinfo.xls&quot.ActiveSheet.Range("D9&quot = Workbooks("BudgetExample.xls&quot.ActiveSheet.Range("E11&quot
Workbooks("BudgetExample.xls&quot.ActiveSheet.Range("F11:Q11&quot.Copy Destination:=Workbooks("2003budgetinfo.xls&quot.ActiveSheet.Range("A14&quot


'Save the workbook before sending
Workbooks("2003budgetinfo.xls&quot.Save

'fetch workbook info
strpath = Workbooks("2003budgetinfo.xls&quot.FullName

'create outlook objects
Set olapp = CreateObject("Outlook.Application&quot
Set oitem = olapp.CreateItem(olMailItem)
Set Allattachments = oitem.Attachments

'create attachment
Set currentAttachment = Allattachments.Add(strpath)

With oitem
.Subject = "your subject"

.To = strrecipient
.Body = "YOUR BODY MESSAGE"
.Save
End With

'cleanup
Set oitem = Nothing
Set Allattachments =Nothing
Set currentAttachment =Nothing
Set olapp = Nothing

End Sub

 

[kylua]

You say it's what you've got. Does it work, does it do what you want it to do? Don't keep me in suspense here.

Or what does it do if it doesn't work?

I can't help with the outlook object unfortunately.

If it does work, I'm keeping it for when we get to outlook at work. (Erm, still got Celeron 233s, not holding my breath for any quick upg here)

 

[Rexxx]

The code above allows the form to address and attach the file then save to Outlook draft by fetching the address from an Excel cell. I had to make other modifications to this code too so that it would continue to loop and save to a file by fetching a name from two Excel cells. I'll post it here when I'm completely finished in case someone else finds it useful.