q Emailing Excel Sheet With Outlook

I am trying to send a table from Excel within the body of an Outlook
(lastest versions) to be sent as a fax.  Am able to do this, BUT when
the document prints out on the fax machine the formating is off (too
big for the sheet).  I am flexible on changing the method I send it to
the fax machine, however it must be sent to the fax via macro.  Below
is the code I am using.

Any help would be greatly appreciated.

David

Public Sub DoIt()
   'On Error GoTo Handler
   Dim EmailAddress(0 To 2) As String
   Dim Count As Integer
   Dim N As Integer
   Dim sRec1(0) As String
   Dim sRec2(0 To 1) As String
   Dim sRec3(0 To 2) As String

   Count = 0

    'If Range Email Address1 countains a valid email address then
assign it to a slot in the EmailAddress array
    If Len(Range("EmailAddress1").Value) > 2 Then
        EmailAddress(Count) = Range("EmailAddress1").Value
        Count = Count + 1
    End If

    'If Range Email Address2 countains a valid email address then
assign it to a slot in the EmailAddress array
    If Len(Range("EmailAddress2").Value) > 2 Then
          EmailAddress(Count) = Range("EmailAddress2").Value
         Count = Count + 1
    End If

    'If Range Email Address3 countains a valid email address then
assign it to a slot in the EmailAddress array
    If Len(Range("EmailAddress3").Value) > 2 Then
        EmailAddress(Count) = Range("EmailAddress3").Value
        Count = Count + 1
    End If

    If Count = 0 Then
      MsgBox "There were no valid email addresses or fax numbers,
please send manually."
      Application.Quit
    End If


    If Count = 1 Then
        sRec1(0) = EmailAddress(0)
        EmailActiveSheetInBody sRec1, "Order Confirmation - Test"
    End If

     If Count = 2 Then
        sRec2(0) = EmailAddress(0)
        sRec2(1) = EmailAddress(1)
        EmailActiveSheetInBody sRec2, "Order Confirmation - Test"
    End If

     If Count = 3 Then
        sRec3(0) = EmailAddress(0)
        sRec3(1) = EmailAddress(1)
        sRec3(2) = EmailAddress(2)

        EmailActiveSheetInBody sRec3, "Order Confirmation - Test"
    End If




Exit Sub
Handler:
MsgBox "An error has occured, email and or fax confirmations have not
been sent.  Please check email addresses and/or fax numbers."
Application.Quit

End Sub



Public Sub EmailActiveSheetInBody(rasRecipients() As String, _
 rsSubject As String)

 On Error GoTo Handler
    SendHTMLEmail rasRecipients, rsSubject, sGetActiveSheetHTML

Exit Sub
Handler:
MsgBox "An error has occured, email and or fax confirmations have not
been sent.  Please check email addresses and/or fax numbers."
Application.Quit

End Sub

Private Function sGetActiveSheetHTML() As String

    Dim sFullName As String
    Dim fso As Scripting.FileSystemObject
    Dim fsoTS As Scripting.TextStream

    Application.ScreenUpdating = False
    sFullName = Environ$("temp") & Application.PathSeparator _
     & Format$(Now(), "yymmddhhmmss") & _
     Str(Timer * 100)
    ActiveSheet.Copy
    With ActiveWorkbook
        .Sheets(1).SaveAs sFullName & ".htm", xlHtml
        .Close False
    End With

    Set fso = New Scripting.FileSystemObject
    Set fsoTS = fso.GetFile(sFullName & _
     ".htm").OpenAsTextStream(ForReading, TristateUseDefault)
    sGetActiveSheetHTML = fsoTS.ReadAll
    fsoTS.Close
    Set fsoTS = Nothing
    Set fso = Nothing
    Kill sFullName & ".htm"
    Application.ScreenUpdating = True


End Function

Private Sub SendHTMLEmail(rasRecipients() As String, _
 rsSubject As String, rsHTMLBody As String)

    Dim olApp As Outlook.Application
    Dim olMI As Outlook.MailItem
    Dim nRecip As Integer

    Set olApp = GetObject("", "Outlook.Application")
    Set olMI = olApp.GetNamespace("MAPI").GetDefaultFolder( _
     olFolderInbox).Items.Add
    With olMI
        For nRecip = LBound(rasRecipients) To UBound(rasRecipients)
            .Recipients.Add rasRecipients(nRecip)
        Next nRecip
        .Subject = rsSubject
        .HTMLBody = rsHTMLBody
        .Send
        On Error Resume Next
        Do Until olApp.GetNamespace("MAPI").GetDefaultFolder( _
         olFolderOutbox).Items.Count = 0
            DoEvents
        Loop
        On Error GoTo 0
    End With
    Set olMI = Nothing
    Set olApp = Nothing

End Sub


0
dlev (1)
9/1/2003 12:18:20 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
218 Views

Similar Articles

[PageSpeed] 57

Reply:

Similar Artilces:

pgp error when opening Outlook 2000 Pro.
Please help. I have a computer that is running Windows XP Home and MSOffice 2000 Pro. Anyhow, when I try to open Outlook, it gives an error "pgpexch.dll" and says that virtual memory may be set to low. I don't know if the problem is with the file or with the virtual memory settings. I've gone into the virtual memory and set it to let Windows manage it but I still get the error. What can I do? Thanks in advance for your help. Sincerely, Rodney Brown ...

outlook over RPC new auth behaviour introduced?
I've been trying to isolate a strange issue with OORPC. Prior to some recent patching, a user would be able to log on to outlook over RPC without a workstation being a member of the domain by installing the relevant certificates and configure and setting up outlook as usual. Additionally your local cached login ID (say for example, a domain laptop was offsite and you logged in with cached info) did not seem related to whether you could log into an outlook over RPC account for a different user so long as you were using different outlook profiles. Now it seems that OORPC is only work...

Incoming Emails 01-15-10
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Email Client: pop Why am I suddenly getting the same emails sent to me over and over again whenever I log on? Every time I log on, I now get emails from last week that I've already received and even deleted. On 1/14/10 5:47 PM, in article 59bb0f88.-1@webcrossing.JaKIaxP2ac0, "Trdwnds@officeformac.com" <Trdwnds@officeformac.com> wrote: > Why am I suddenly getting the same emails sent to me over and over again > whenever I log on? Every time I log on, I now get emails from last week that > I've al...

How do you set up a submit button to attach worksheet in email?
I am doing a form and I want to have a submit or send link on the bottom of the page. I know how to make hyperlinks but I dont know how to get it where when the user hits the send button it automatically saves that info and sends that form to the intended email address. Basically so you dont have to attach it yourself through email. Is there a way to do this? Have a look here for code to send mail http://www.rondebruin.nl/sendmail.htm#Tips -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on a...

Excel 07 selects 3 cells when I click on 1
Hi I just got 2007 (I want 2000 back) and a lot of the time when click on an empty cell 2 or more below it are selected and the typing goes on the bottom row. I just want to select the cell I click on. Thanks Are the cells merged?? I don't like the 2007 either! If i could i would have the look and feel of 2003 on the engine of 2007... :-( On Thu, 1 Nov 2007 20:21:01 -0700, Brian <Brian@discussions.microsoft.com> wrote: >Hi I just got 2007 (I want 2000 back) and a lot of the time when click on an >empty cell 2 or more below it are selected and the typing goes on the botto...

Excel 2003/edit/replace. Under Options/Look In, values is AWOL bu.
Under Look In options Find allows for search by formulae OR values OR comments Replace is restricted to formulaes ONLY WHY? cp Have a look at this google search result. This topic came up a couple days ago and was addressed by Dave and Myrna. http://snipurl.com/cd9j Gord Dibben Excel MVP On Fri, 28 Jan 2005 03:49:02 -0800, "cp" <cp@discussions.microsoft.com> wrote: >Under Look In options >Find allows for search by formulae OR values OR comments >Replace is restricted to formulaes ONLY >WHY? ...

Cannot read Base64 encoded emails (OL98)
Hello folks, I have a problem with one of my Outlook 98 machines. One user (Ironically the boss!) cannot read base64 encoded emails, they come up as garbage, which I then have to run through a Base64 decoder. Other users also use Outlook 98, but can read the emails fine. the problem affects the message text and encoded objects. Mail is coming from the same local server (602 ProLan Suite). The machine with the error is running build 8.5.7806.0. The other machines (Which read messages fine) are both running build 8.5.5104.6. This leads me to thinking that the problem is a result of a ...

how do i enable dde in excel?
i am posting a DDE link into an excel cell from an external program and getting the message ' DDE Not enabled' in the cell once the link is pasted in. how do i enable DDE? ...

Sending email but outbox is empty
Hi NG, When I press send/receive Outlook shows in its status bar that it works on sending emails (Sending messages 1 of 4) but there is not mail in outbox. As a result I currently cannot send any other mails since Outlook keeps working on the 4 emails I don't see. Any ideas, I am clueless? - Thanks a lot for any help!!!! Dirk ...

set up email 07-14-10
hello.i am trying to set up an email account on my laptop and my modem is my Blackberry phone.everytime i try to set it up it comes back with all kinds of problems.my email on the phone works great,but i would rather have an account on my laptop.my phone is T-Mobile Blackberry Bold.i would be very thankful for any help. "Looster59" <Looster59@discussions.microsoft.com> wrote in message news:5BE71BBC-D2DC-44F8-8C95-6F0810E00124@microsoft.com... > hello.i am trying to set up an email account on my laptop and my modem is > my > Blackberry phone.everytime...

Synchronise Outlook 2003
I am using outlook 2003 on a laptop. I would like to sync my changes with my assistant outlook 2003 when I get back to the office. Is it possible to sync contacts ? I do not want to use a central data base but really sync 2 different data bases. thanks phil <phil6987@voila.fr> wrote: > I am using outlook 2003 on a laptop. I would like to sync my changes > with my assistant outlook 2003 when I get back to the office. Is it > possible to sync contacts ? > I do not want to use a central data base but really sync 2 different > data bases. http://www.slipstick.com/outlook/...

excel file can't be opened #2
hi,I'm cho I have a recent excel file (about 1 month),but since two days ago,the file can't be opened completely or always not responding. What should I do so that I can use this important file. Any help would be appreciated regards, cho ...

How to transpose in excel file
Hi I would like to make a transpose like this? From House Defect How much #1 Broken Sinks 1000 #1 Bad Paint 2000 #1 Bad Lighting 1000 #2 Broken Sinks 0 #2 Bad Paint 2000 #2 Bad Lighting 100 and Transpose into House Broken Sinks Bad Paint Bad Lighting #1 1000 2000 1000 #2 0 2000 100 A Pivot Table will do that and more. Set House as row field, Defect as column field and How much as data. HTH. Best wishes Harald "vilfood" <vilfood@d...

new email notification doesn't work?
In Outlook 2003, I have the option set for the semi-transparent notification window to pop up when I get new email, but it never does. Is there something I'm not doing right? Does it only come up in certain situations? Btw, I'm using the regular Inbox with a POP3 account. -- John Salerno Do you have rules moving your email to folders after receipt? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searchi...

Sharing Outlook #2
I want to set up three account on my outlook. I have already set up two of them. Is there any way of switching from profile to profile while in outlook? nope...afaik... "PaulaDawn" <blah@blah.net> wrote in message news:OHC8VWbSEHA.3692@TK2MSFTNGP10.phx.gbl... > I want to set up three account on my outlook. > I have already set up two of them. Is there any way of switching from > profile to profile while in outlook? > > Only by shutting down and restarting - switching MAPI profiles is not as easy as switching identities in Outlook Express. --� Milly Stap...

Outlook user profiles
I set-up two different user profiles in outlook. I am not able to change the settings in one user profile under Tools/options/mailset-up without it changing the settings on the other user profile. I need to apply individual settings for each. Anyone know how to remedy this? I have tried everything! Which settings are youi trying to change? -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "gia" <anonymous@discussions.microsoft.com> wrot...

Outlook 2003 cuts off lines
Hi! I synchronize my PDA daily and as part of its software, Newsbreak, I can send myself emails of interesting stories with URL links to look up online later when I get home and connect. When I load them into Outlook 2003 and send them to myself, very long URLs appear correct in the Outbox version of the emails. When I end up receiving the emails back INTO Outlook, it cuts off lines beyond a certain length, thereby rendering the URLs broken. Now I can look in the Sent Items and click the good version of the URL but I would really love to know how you keep Outlook 2003 from hatcheting...

Headers in Excel #3
Can you tell me if it's possible and if so, how I can change the header cells, i.e. A B C D E ... etc? Thanks. Andrea (508) 842-3880 Hi Andrea no this is not possible. The best workaround you can achieve is: - use row 1 as header - goto cell A2 and to the menu 'Windows - Freeze Panes' to always show row 1 - in 'Tools - Options - view' you can hide the row and column headers -- Regards Frank Kabel Frankfurt, Germany "Andrea" <shrews.receptionist@nitco-lift.com> schrieb im Newsbeitrag news:12ff301c44354$b88982b0$a501280a@phx.gbl... > Can you tell ...

Outlook 2003 attach File Browse Window Stops Responding
In outlook 2003 when I try to attach a file and the browse window pops open to browse for the file I want to attach the window will stop responding and I have to force quit the outlook. Any Ideas? do you have mapped network drives? how long do you wait before quitting outlook? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipsti...

Hiding / Unhiding Sheets
I have created macros to hide and unhide sheets when clicking on a button example - Sheets("Premium Labor Utilization").Visible = Not Sheets("Premium Labor Utilization").Visible I am also assigning goups of sheets to a summary button example Application.ScreenUpdating = False Sheets("Premium Labor Utilization").Visible = Not Sheets("Premium Labor Utilization").Visible Sheets("FMLA Compliance").Visible = Not Sheets("FMLA Compliance").Visible Sheets("FLSA Compliance").Visible = Not Sheets("FLSA ...

Protecting a sheet (yet allowing buttons/macros to work)?
I'm new to the button/macro feature of Excel (even though I've been assigning macros to _toolbars_ for years <g>!) So, I don't even know if what I need to do can be done: Ideally, it would be nice if I could protect all the sheets or even better the workbook in one fell swoop, so that no-one could accidentally delete the buttons YET that would still allow the macros to work. I've tried everything I know and the best I seem to be able to do, yes, protects the buttons but I the macros stop working and display a ""Run-time error '1004'". T...

Sending URL in Outlook XP
When I try to send a page by email from IE 6, Outlook displays a message saying Outlook has blocked access becuase of potentially unsafe. Is there a work around for this. I used to be able to send the page as an icon or send the page as a link. How can I get these features back? Thanks, Mark ...

Can't access comcast newsgroups in outlook express 6 or Outlook 2003
Hi, I had the comcast news server working in both aps, but trying to make it work with outlook 2003 as the default program to access the newsgroup reader, by removing the newsgroup account in outlook express 6, I now can't get it to connect in either ap. duh... It's completely gone from Outlook 2003, ie, where before, under the go button there was a link to news, now there is not. Outlook express 6 still has the newsgroup reader, that's how I'm sending this, but when i try to go to the comcast news server it repeatedly asks for my username and password. I deleted the a...

Outlook Signatures #4
I have 3 mail accounts, each with a different signature. When I am composing a new email and select a different account to send from the sinature does not change. I can of course change it manually, but it doesn't work automaically. Any suggestions please ? Graham Redman <grahamredman@btinternet.com> wrote: > I have 3 mail accounts, each with a different signature. > > When I am composing a new email and select a different > account to send from the sinature does not change. > > I can of course change it manually, but it doesn't work > automaically. P...

Excel -> Access Import Bizarreness
A guy gave me an Excel file that he needs me to covert to XML. No problem. Slurp it into a database and use a DataSet.WriteXML() call to turn it into an XML file. But I can't import it into Access because it has "Merged Cells". For example, data that should look like this: ColA ColB ----- ----- Rec1 this Rec2 this Rec3 this Rec4 that Rec5 Other The three "this" cells have been merged into one tall cell: ColA ColB ----- ----- Rec1 this Rec2 Rec3 Rec4 that Rec5 Other I need to "unmerge" these, but I know almost nothi...