1

We are planning to upgrade all our users to Outlook 2010 and Exchange 2010 soon but have run into a few headaches with the Outlook migration. We have an Excel spreadsheet that is basically an expense report that automatically gets emailed to the appropriate people when they click a button in the spreadsheet. It uses some macro (I'm not a programmer) to achieve this. It works well with Outlook 2003 that we have now, but our test group of users cannot use it - they receive the error below.

Can anyone help figure this out. Its driving me nuts!!

I've tried sending it with Outlook open and with it closed. enter image description here

UPDATE: SOLVED

The problem was our citrix farm setup...Since MS doesnt allow side-by side installs of outlook versions like they do with all other office applications, we had to install outlook 2010 on a "testing" xen app server. Since these users were test outlook 2010 users their profiles only had permissions to use outlook 2010 and outlook 2010 as their default email client. The problem presented itself when they would open the spreadhseet in excel 2003 on the production xenapp farm - where outlook 2010 wasn't installed. so it was trying to send mail through their default email client (outlook 2010) which didn't exist on that server. And since their accounts were only configured to use outlook 2010, excel didn't know what to do.

The solution for us was to create a new Excel published app in citrix that was installed on the same server as our outlook 2010 installation, limit the farm to that one testing server, and publish that app to our test users, while removing their old excel app. This way, the are only running Outlook and Excel on the test server.

1 Answer 1

1

I can only show you the code that I had to go to so that my email triggered off a button in an Excel spreadsheet would work. They changed some things, so the old code didn't work so well.

Private Sub EmailBlahbutton_Click()

Dim mOutlookApp As Object
Dim OutMail As Object
Dim Intro As String

On Error GoTo ErrorHandler

Set mOutlookApp = GetObject("", "Outlook.application")
Set OutMail = mOutlookApp.CreateItem(0)

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

'These are the ranges being emailed.
ActiveSheet.Range(blahblahblah).Select

'Intro is the first line of the email
Intro = "BLAHBLAHBLHA"

'Set the To and Subject lines.  Send the message.
With OutMail
    .To = "[email protected]"
    .Subject = "More BLAH here"
    .HTMLBody = Intro & RangetoHTML(Selection)
    .Send
End With

With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With

ActiveSheet.Range("A1").Select
ActiveWindow.ScrollColumn = ActiveCell.Column
ActiveWindow.ScrollRow = ActiveCell.Row

Set OutMail = Nothing
Set mOutlookApp = Nothing

Exit Sub

ErrorHandler:
    Set mOutlookApp = CreateObject("Outlook.application")
    Resume Next

End Sub

Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2010
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook

TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
  .Cells(1).PasteSpecial Paste:=8
  .Cells(1).PasteSpecial xlPasteValues, , False, False
  .Cells(1).PasteSpecial xlPasteFormats, , False, False
  .Cells(1).Select
  Application.CutCopyMode = False
  On Error Resume Next
  .DrawingObjects.Visible = True
  .DrawingObjects.Delete
  On Error GoTo 0
End With

'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
  SourceType:=xlSourceRange, _
  Filename:=TempFile, _
  Sheet:=TempWB.Sheets(1).Name, _
  Source:=TempWB.Sheets(1).UsedRange.address, _
  HtmlType:=xlHtmlStatic)
  .Publish (True)
End With

'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                      "align=left x:publishsource=")

'Close TempWB
TempWB.Close savechanges:=False

'Delete the htm file we used in this function
Kill TempFile

Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing

End Function
1
  • Thanks your code worked. See updated problem for the real resolution. I'm still giving you credit as your solution led me down the right path. Thanks again. Jun 8, 2011 at 15:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .