Posts Tagged MsgBox

Email Excel Spreadsheet as Email Body Issues

Hello all. I had a production manager wanting an excel spreadsheet mailed as the body of the email. As some of you know the code generated by excel to produce the email is pretty crazy. But as a result, it showed up fine in Outlook and Android but it did not show the gridlines on the spreadsheet. So this code is based on the excellent work by Ron DeBruin over at http://www.rondebruin.nl/win/s1/outlook/bmail3.htm . I did a replacement for the HTML Range in this manner and the grid lines did appear. And the manager was happy.

Sub Mail_Selection_Range_Outlook_Body()
‘For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
‘Don’t forget to copy the function RangetoHTML in the module.
‘Working in Excel 2000-2016
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
‘MsgBox Cells(5, 9).Value
    Set rng = Nothing
    On Error Resume Next
    ‘Only the visible cells in the selection
    Set rng = Selection.SpecialCells(xlCellTypeVisible)
    ‘You can also use a fixed range if you want
    ‘Set rng = Sheets(“YourSheet”).Range(“D4:D12”).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If rng Is Nothing Then
        MsgBox “The selection is not a range or the sheet is protected” & _
               vbNewLine & “please correct and try again.”, vbOKOnly
        Exit Sub
    End If

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

    Set OutApp = CreateObject(“Outlook.Application”)
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .BodyFormat = olFormatHTML
        .To = “you@you.com”       
           
               
       
         .CC = “”
        .BCC = “”
        .Subject = “Testing Purchase Order Email To Steve”
        .HTMLBody = RangetoHTML(rng)
        Replace .HTMLBody, “border-left:none”, “border-left:solid;border-width: 1px;border-color:black”
        Replace .HTMLBody, “border-right:none”, “border-right:solid;border-width: 1px;border-color:black”
        Replace .HTMLBody, “border-bottom:none”, “border-bottom:solid;border-width: 1px;border-color:black”
        Replace .HTMLBody, “border-top:none”, “border-bottom:solid;border-width: 1px;border-color:black”
        .Send
         ‘or use .Display
    End With
    On Error GoTo 0

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

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Advertisements

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Leave a comment

Getting Images from Microsoft Office Document Imaging (MODI) with vb.net

Hello! It has been a long time since my last blog post and I am happy to be getting back to this. I have missed writing this blog more than you have missed me I guarantee it!

That being said, now that this blog has resumed there will be a few changes. The landscape has changed a bit since my last blog entry. While I will still focus primarily on vb.net code I will also be including what I am working on that is WPF and C# related as well as other .NET related topics. Why? Simply because as my skillset has expanded I wanted to share what I have learned and am learning with you as well. I hope you won’t mind!

Today’s topic revolves around getting images from Microsoft Office Document Imaging (MODI). As you recall I have done a lot of Office automation over the years and I was asked to do this for a friend. As I said, it is good to be back!

add a reference -> Com -> Microsoft Office Document Imaging

Private Sub ConvertMdi2Tif(ByVal ModiFilePath As String)
Dim miDoc As New MODI.Document
Dim myViewer As New MODI.MiDocView
Dim myImg As MODI.Image
Try
Dim Folder As New DirectoryInfo(ModiFilePath)
pbReportGenerate.Minimum = 0
pbReportGenerate.Maximum = Folder.GetFiles(“*.MDI”).Length
pbReportGenerate.Step = 1
pbReportGenerate.Value = pbReportGenerate.Minimum
For Each File As FileInfo In Folder.GetFiles(“*.MDI”)
miDoc.Create(File.FullName)
myViewer.Document = miDoc
For i As Long = 0 To miDoc.Images.Count – 1
Dim tempDoc As New MODI.Document
myImg = miDoc.Images(i)
tempDoc.Create()
tempDoc.Images.Add(myImg, Nothing)
tempDoc.SaveAs(TIFPath & “\” + GetFileNameWithoutExtension(File.FullName) & i & “.Tif”, MiFILE_FORMAT.miFILE_FORMAT_TIFF_LOSSLESS, MiCOMP_LEVEL.miCOMP_LEVEL_HIGH)
tempDoc.Close()
tempDoc = Nothing
Exit For
Next
pbReportGenerate.PerformStep()
Application.DoEvents()
Next
miDoc.Close()
miDoc = Nothing
myViewer.Document = Nothing
myViewer = Nothing
Catch ex As Exception
MsgBox(ex.Message)
End Try
GC.Collect()
End Sub

Private Sub ConvertTif2Bmp(ByVal TIFFilePath As String)
Dim Folder As New DirectoryInfo(TIFFilePath)
Dim index As Long = 0
pbReportGenerate.Minimum = 0
pbReportGenerate.Maximum = Folder.GetFiles(“*.TIF”).Length
pbReportGenerate.Step = 1
pbReportGenerate.Value = pbReportGenerate.Minimum
For Each File As FileInfo In Folder.GetFiles(“*.TIF”)
GC.WaitForPendingFinalizers()
Dim streamBinary As New FileStream(File.FullName, FileMode.Open)
Dim g As System.Drawing.Image = System.Drawing.Image.FromStream(streamBinary)
Dim imgOutput As New Bitmap(g, g.Width, g.Height)

Dim qualityEncoder As Encoder = Encoder.Quality
Dim ratio As EncoderParameter = New EncoderParameter(qualityEncoder, 40)
Dim codecParams As New EncoderParameters(1)
codecParams.Param(0) = ratio
‘bmp.Save(fileName, jpegCodecInfo, codecParams)
Dim encoder1 As ImageCodecInfo = GetEncoderInfo(“image/bmp”)
imgOutput.Save(BMPPath + “\” + GetFileNameWithoutExtension(File.FullName) + “.” & System.Drawing.Imaging.ImageFormat.Bmp.ToString, encoder1, codecParams)
streamBinary.Close()
streamBinary = Nothing
g.Dispose()
g = Nothing
imgOutput.Dispose()
imgOutput = Nothing
pbReportGenerate.PerformStep()
Application.DoEvents()
Next
GC.Collect()
End Sub


My Facebook

WordPress Tags: vb.net,Images,Microsoft,Office,Document,MODI,automation,friend,reference,MiDocView,Image,Folder,DirectoryInfo,Minimum,Maximum,GetFiles,Length,Step,Value,File,FileInfo,Create,FullName,Long,Count,SaveAs,TIFPath,GetFileNameWithoutExtension,MiFILE_FORMAT,MiCOMP_LEVEL,Close,Exit,PerformStep,Application,DoEvents,Catch,Exception,MsgBox,Message,Collect,TIFFilePath,index,WaitForPendingFinalizers,FileStream,FileMode,Open,System,FromStream,Bitmap,Width,Encoder,ratio,EncoderParameter,EncoderParameters,Param,Save,ImageCodecInfo,GetEncoderInfo,BMPPath,ImageFormat,Dispose,blog,miDoc,myViewer,myImg,pbReportGenerate,tempDoc,streamBinary,imgOutput,qualityEncoder,codecParams

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

2 Comments