Email from excel with attachment and subject 1

[Tiglet]

I have a whole load of excel s/sheets to send out so in a separate s/sheet I have a list of filenames in Col A, the recipients in Col B & subject in Col C (See code below). However, what I can't do is send email with attachment and message body (rather than subject). Please could anyone suggest a solution. I know it can be done through Outlook but we run Groupwise at work and so Outlook is NOT an option. It therefore has to be done from Excel.

Thanks

PS. If anyone could post a code solution would they mind very much putting the odd comment in so I can understand what it does - I am a novice

Thanks in advance

 Dim strThisCC As String, strSheetName As String
    Dim recipient As String, subject As String
    Do While True
    strThisCC = ActiveCell.Value
    If strThisCC = "" Then Exit Sub
    ActiveCell.Offset(0, 1).Activate
    'move to next column
    recipient = ActiveCell.Value
    ActiveCell.Offset(0, 1).Activate
   'move to next column
    subject = ActiveCell.Value
    strSheetName = strThisCC
    Workbooks.Open (strSheetName)
    ActiveWorkbook.SendMail recipient, subject
    ActiveWorkbook.Close
    ActiveCell.Offset(1, -2).Activate
    Loop
    End Sub

 

[Nikita6003]

Hiya,

give this link a try:

http://www.mvps.org/access/modules/mdl0059.htm

It's a groupwise class writtem for Access but adapts easily to Excel

HTH

Cheers
Nikki

 

[Tiglet]

Nikki,

thanks for the link, I've had a look but can't make head-nor-tail of it!! I wouldn't have a clue how to make it work in excel

 

[Nikita6003]

Dunno how right/wrong this is - long time ago & haven't got the GroupWise object model here, but it might get you started

    With l_appGroupware.Login.MailBox.Messages.Add(Class:="GW.MESSAGE.MAIL")
        'Add recipients
        .Recipients.Add "Recipient1Name"
        .Recipients.Add "Recipient2name"
        'etc."

        'Add sender name
        .FromText = "NameOfSender"
        
        'Add subject
        .Subject.PlainText = "SubjectText"
        
        'Email text
        .BodyText.PlainText = "EmailBodyText"

        'Add xl attachment
        .Attachments.Add "FullPathAndFileName"

        .Send
    End With

Cheers
Nikki

 

[Tiglet]

Thanks Nikki,

Quick question, should I put this code into Excel or into Groupwise?

Tiglet

 

[Nikita6003]

Hi Tiglet,

It was based on excel:
Make sure you set a ref to the groupwise object (btw - Novell has a complete description of the object model on their site - very useful!

http://developer.novell.com/ndk/doc.../ndk/doc/gwobjapi/gwobjenu/data/h4r4bw6c.html

will get you to the Message object - this is what I based the code on)
use

CreateObject

to set the l_appGroupWise object
Use code to create/send email
The code will attach whatever file you specify; if the workbook you want to send = open, just use

.Attachments.Add Application.Workbooks("NameOfWorkbook")

HTH!

Cheers
Nikki

 

[Tiglet]

Nikki,

You are wonderful, I went onto Novell's site and wandered aimlessly for a while, I will check this out and let you know if it works.

Tiglet

 

[Tiglet]

Nikki, btw, when I said I wandered on Novell's website, I mean't before you sent me the link!!!!

 

[Tiglet]

Nikki,

Life sucks!! Our IT department got really funny about me automating mail so they won't let me do it. Please accept my apologies for asking for your help and then being unable to put it too good use.:~/

I'm going away to cry now - it's so frustrating!!>:-<

I.T. Departments - The Price Of Progress!!:-D

 

[Nikita6003]

Ow!

Don't worry - WE have *eight* IT departments dedicated to supporting the network - each has their own little bit to do & if one says something = OK you can bet your LIFE the second or third or fourth or ... will say no no no that's entirely against procedures.

Example: had to adapt a VB 5 .exe using VB 5. Had to ask them to install VB 5 on my PC and they cxame back sorry n o can do we found the official cd's BUT we've lost the registration key (d'OH) so you can just go back to your dept and rewrite the thing in C++ coz we do have a licence for C++ if you're interested. So I offered them either 3 weeks to rewrite at EUR 10000 or buy an new VB 5 cd + about 8 hours to do the change at at EUR 1250 and they said too bad but there's a stop on buying IT equipment & apps & we'll never be able to get approval ...

So I went away & learnt all about C++ in the process. Took me longer than the projected 3 weeks (go figure ;-) but hey that's their problem.

So I *definitely* sympathise


Cheers
Nikki
about to jump off cliffs - cu March 10th

 

[Tiglet]

Nikki,

I finally got round our IT department and SUCCESS!!, your posts were really helpful so I've given you some stars. Thanks again

Tiglet