SendObject like module??? 1

[RookieDBO]

Could someone or everyone help me in creating a module that is similar to the "SendObject" macro? I've created a database that extracts and manipulate data for my inventory managers. I then use the SendObject Macro to distribute the final table in an Excel format. This is a daily process and works perfectly. However, I want to format the spreadsheet layout before it emails it out. Is that possible? Unfortunately, don't know much about VB. Please help. I want to do the following with the spreadsheet.

-Add grid lines
-Center the data
-Bold the column headers
-Format print layout i.e. Center and title



Thanks
RookieDBO

 

[jtmach]

What your are trying to do requires a bit more then just another method of sendObject. What I would suggest would be the following. Make the excel spreadsheet the way that you want it to be. Convert your access stuff to write to the piticular cells in that spreadsheet. That way access will have done that part of the work for you, and you will have a formatted spreadsheet all ready to go. Unfortunatly, the sendobject command cannot send an e-mail with an attachment that is outside of access. However I have a module that can. If you get the previous stuff done. I would be more then happy to share the module with you. The hardest questions always have the easiest answers.

 

[RookieDBO]

Yes I have done what you've suggested. May I have the module that you've offered to share? I would really appreciate it. Is this the only way to do this?

Thanks,
RookieDBO

 

[jtmach]

Copy this function into a new module, make sure you go to "Tools", "References" and check "Microsoft Outlook 9.0" or "Microsoft Outlook 8.0"

Public Function SendEmail(msgTO As String, Optional msgCC As String, Optional msgSubject As String, Optional msgBody As String, Optional AttachmentPath As String = "None", Optional ImportanceHigh As Boolean = False, Optional SendNow As Boolean = False)
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
        Set objOutlook = CreateObject("Outlook.Application")
        Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
        With objOutlookMsg
            .To = msgTO
            .CC = msgCC
            .Subject = msgSubject
            .Body = msgBody
                        
            If AttachmentPath <> &quot;None&quot; Then
                'if your are not adding an attachment do not put anthing in this argument when calling the function
                .Attachments.Add AttachmentPath
            End If
            
            If ImportanceHigh = False Then
                .Importance = olImportanceNormal
            Else
                .Importance = olImportanceHigh
            End If
            
            'Resolving the message removes all invalid e-mail address, this will handle up to 5 invalid email addresses
            Dim i As Integer
            i = 1
            Do While i < 5
                For Each objOutlookRecip In .Recipients
                    objOutlookRecip.Resolve
                    If Not objOutlookRecip.Resolve Then
                        objOutlookRecip.Delete
                    End If
                Next
                i = i + 1
            Loop
            
            If SendNow = False Then
                .Display (False)
            ElseIf SendNow = True Then
                .Send
            End If
        End With
    Set objOutlookMsg = Nothing
    Set objOutlook = Nothing
End Function

Once you have done this you can use this function anywhere by typing

SendEmail "Me@Myhouse.com","Me@Myhouse2.com" ,"MySubject" ,"MyBody" ,"C:\MyDocument" , True, False

The hardest questions always have the easiest answers.

 

[RookieDBO]

It would not run. I copied it and only made changes to the last line. I changed the email address, subject, the path and file name. Also I named the module "Test". What am I doing wrong? Have you tried this with Access '97?

Thanks,
Joe

 

[jtmach]

Yes, I have ran this function thousands of times using Access 97 and Microsoft Outlook. Did you get an error message saying why it would not run. You have to put the last line in the event handler that you want to make it run. For instance if you want this program to run when you click a button. You have to put it in the on-click event of the button.

or try this. Replace the last line with the three lines below. Then you can use this by making a macro with a runCode commmand, and the argument TestEmail as the code to run.

Public function TestEmail()
SendEmail "Me@Myhouse.com","Me@Myhouse2.com" ,"MySubject" ,"MyBody" ,"C:\MyDocument" , True, False
end function The hardest questions always have the easiest answers.

 

[RookieDBO]

Still would not run. Using the macro concept is a good idea cause I have an AutoExec macro. I inserted a "RunCode" macro and pointed to the SendEmail but I get and error saying "Can't find the name SendEmail". I also I tried with the replacement codes. Also I get an error.
What else should I try?

Thanks,
Joe

 

[jtmach]

After replacing the one line with the three lines. Put "TestEmail()" in the argument for your runcode line in the macro. The hardest questions always have the easiest answers.

 

[RookieDBO]

I give up!!! Can't get it to work. Thanks for the help anyways.

 

[jtmach]

If you want to give it one more try. If the database is small compact it, zip it and e-mail it to jtmac.geo@yahoo.com and I will fix it and e-mail it back to you The hardest questions always have the easiest answers.

 

[RookieDBO]

That's great. I will do so.

Thanks

 

[RookieDBO]

I get a delivery failure message. It says your account is not valid. Any other email address?

Thanks.

 

[jtmach]

Sorry, try jtmacho@yahoo.com The hardest questions always have the easiest answers.

 

[RookieDBO]

One more thing. I have to email the report to 4 recipients.
I tried the following.

Public Function TestEmail()
SendEmail "bgorsk@ngc.com", "msamso@ngc.com", "mminnic@ngc.com", "gougan@ngc.com", "Test", "Weekly Automated In/Out Report", "D:\(Joe) Supply Chain\In_Out\Upcoming_In_Out_Promo_Week.XLS", False, True
End Function

It would not run. Is this possible or am I just doing something wrong?

Thanks,
RookieDBO

 

[jtmach]

Just ust it like this

SendEmail "bgorsk@ngc.com;msamso@ngc.com;mminnic@ngc.com;gougan@ngc.com;", "Test", "Weekly Automated In/Out Report", "D:\(Joe) Supply Chain\In_Out\Upcoming_In_Out_Promo_Week.XLS", False, True
 The hardest questions always have the easiest answers.

 

[RookieDBO]

No luck. The macro runs but I don't get any message. It doesn't send anything. Anymore suggestion? I have this group in my address book. Would that help?

Thanks,
RookieDBO

 

[jtmach]

Sorry, like this

SendEmail "bgorsk@ngc.com;msamso@ngc.com;mminnic@ngc.com;gougan@ngc.com;", , "Weekly Automated In/Out Report","Weekly Automated In/Out Report", "D:\(Joe) Supply Chain\In_Out\Upcoming_In_Out_Promo_Week.XLS", False, True
[b]
 The hardest questions always have the easiest answers.

 

[RookieDBO]

Thank you.