Barrie! VB me Again, just starting it off up here.
Posted by Ian on August 02, 2001 11:50 AM
RE: the workbook path thingy for the e.mailing from excel question.
thanks for that because i'll need it eventually.
as i've said i can activate outlook and write the code for the mail recipient and the subject.
what i need is the code to Attach the file from the this workbook.path thingy.
I have so far used Dim Mailthingy as...
and used lines like Mailthingy.subject = "hello"
and Mailthingy.send
But what would the line be for Mailthingy.attach I've tried lots of different things???
also I may have had more luck if i knew outlook a lot better, how do get VBE up in outlook to veiw the help files.
cheers a lot for all the help
Ian
Posted by Jerid on August 02, 2001 12:14 PM
Ian, here is some code I got from a freind that uses outlook, I havent tried the code myself as we don't use outlook.
Hope it helps.
Sub Mailto()
On Error Resume Next
'Object variables
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
'Specific variables
Dim sSubject As String
Dim sMessage As String
Dim iNumOfRecipients As Integer
Dim sRecipientName As String
Dim sAttachment As String
Dim iX As Integer
'Initialize variables
sSubject = Range("B1")
sMessage = Range("B2")
iNumOfRecipients = Range("B3") - 1
'Start at cell B5
For iX = 5 To 5 + iNumOfRecipients
sRecipientName = Cells(iX, 2)
sAttachment = Cells(iX, 3)
Set objOutlook = CreateObject("Outlook.Application")
'Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
'Set up Recipient name
With objOutlookMsg
Set objOutlookRecip = .Recipients.Add(sRecipientName)
objOutlookRecip.Type = olTo
'Set up subject and Body
.Subject = sSubject
.Body = sMessage
' Add attachments to the message.
If Not IsMissing(sAttachment) Then
Set objOutlookAttach = .Attachments.Add(sAttachment)
End If
' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
Exit Sub
End If
Next
'Send the message
.Send
End With
'Clear memory
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Next iX
MsgBox "Done"
End Sub
Jerid
Posted by Ian on August 02, 2001 12:24 PM
Sorry to do this to you Jerrid
'Initialize variables sSubject = Range("B1") sMessage = Range("B2") iNumOfRecipients = Range("B3") - 1 'Start at cell B5 For iX = 5 To 5 + iNumOfRecipients sRecipientName = Cells(iX, 2) sAttachment = Cells(iX, 3) Set objOutlook = CreateObject("Outlook.Application") 'Create the message. Set objOutlookMsg = objOutlook.CreateItem(olMailItem) 'Set up Recipient name With objOutlookMsg Set objOutlookRecip = .Recipients.Add(sRecipientName) objOutlookRecip.Type = olTo 'Set up subject and Body .Subject = sSubject .Body = sMessage ' Add attachments to the message. If Not IsMissing(sAttachment) Then Set objOutlookAttach = .Attachments.Add(sAttachment) End If ' Resolve each Recipient's name. For Each objOutlookRecip In .Recipients objOutlookRecip.Resolve If Not objOutlookRecip.Resolve Then Exit Sub End If Next 'Send the message .Send End With 'Clear memory Set objOutlookMsg = Nothing Set objOutlook = Nothing Next iX MsgBox "Done"
If you know, Pleeease could you add extra 'info to the code, I'm, well, less than good, or bad at VB and this would be most helpful.
Also, don't know if you've seen the original post.
the question was, how do I attach the file (after Workbook.save) that the button_click is on.
The reason is that many people don't know all the apps at work and I thought if there was a template set up that has the e.mail resipiant in the code, bingo do the inputing of figures and save as and e.mail, easy. for them, not for me.
thanks
I will look at this at work tomorrow looks damn fine
Ian
Posted by Ivan F Moala on August 02, 2001 7:40 PM
Re: Sorry to do this to you Jerrid
Jerids code should work fine........just
take out the initializing variables and hardcode
your mail recipients, subject & body and attachments...........Hard code it as a String
variable.
ivan
'Initialize variables sSubject = Range("B1") sMessage = Range("B2") iNumOfRecipients = Range("B3") - 1 'Start at cell B5 For iX = 5 To 5 + iNumOfRecipients sRecipientName = Cells(iX, 2) sAttachment = Cells(iX, 3) Set objOutlook = CreateObject("Outlook.Application") 'Create the message. Set objOutlookMsg = objOutlook.CreateItem(olMailItem) 'Set up Recipient name With objOutlookMsg Set objOutlookRecip = .Recipients.Add(sRecipientName) objOutlookRecip.Type = olTo 'Set up subject and Body .Subject = sSubject .Body = sMessage ' Add attachments to the message. If Not IsMissing(sAttachment) Then Set objOutlookAttach = .Attachments.Add(sAttachment) End If ' Resolve each Recipient's name. For Each objOutlookRecip In .Recipients objOutlookRecip.Resolve If Not objOutlookRecip.Resolve Then Exit Sub End If Next 'Send the message .Send End With 'Clear memory Set objOutlookMsg = Nothing Set objOutlook = Nothing Next iX MsgBox "Done"
Posted by Barrie Davidson on August 03, 2001 6:14 AM
Looks like you're already taken care of.
Sorry for not being able to respond Ian.
Regards,
Barrie
