none
regarding creat an command button to send direct email from userform vba RRS feed

  • Question

  • dears,

    good greeting

    i want to creat an commandbutton to send direct email from userform vba

    i have the following code to export data to pdf document, but i need to creat code to send this pdf document

    in email by direct way ,

    Private Sub CommandButton18_Click()

    Sheet3.Range("A2:I10000").Cells.Clear

    Dim Litem As Long, LbRows As Long, LbCols As Long
    Dim bu As Boolean
    Dim Lbloop As Long, Lbcopy As Long
      
     LbRows = ListBox1.ListCount - 1
     LbCols = ListBox1.ColumnCount - 1
       
        For Litem = 0 To LbRows
        If ListBox1.Selected(Litem) = True Then
              bu = True
              Exit For
        End If
        Next

        If bu = True Then
        With Sheets("SelectedData").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
          
                For Litem = 0 To LbRows
                    If ListBox1.Selected(Litem) = True Then 'Row selected
                      'Increment variable for row transfer range
                      Lbcopy = Lbcopy + 1
                For Lbloop = 0 To LbCols
                           'Transfer selected row to relevant row of transfer range
                .Cells(Lbcopy, Lbloop + 1) = ListBox1.List(Litem, Lbloop)
                           
               Next Lbloop
                    End If
                Next
                For M = 0 To LbCols
                    With Sheets("SelectedData").Cells(Rows.Count, 1).End(xlUp).Offset(0, M).Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = 30
            
            
            Dim WS As Worksheet
    Set WS = Sheets("SelectedData")
    Dim rowRng As Range
    For Each rowRng In WS.UsedRange.Rows
    BlanckCellsCount = WorksheetFunction.CountBlank(rowRng)
    If BlanckCellsCount <> rowRng.Cells.Count Then
    With rowRng
    .Borders.Weight = xlThick
    .Cells.VerticalAlignment = xlCenter
    .Cells.HorizontalAlignment = xlCenter
    End With
    End If
    Next rowRng
             
           
            End With
    Next
            End With
            
        Else
             MsgBox "Nothing chosen", vbCritical
             Exit Sub
             

        End If
        Sheet3.Range("a:i").Cells.Font.Size = 40
        Sheet3.Range("a:i").WrapText = True
        Sheet3.Range("a:i").Cells.HorizontalAlignment = True
        
            
        Sheet3.Range("A:I").ExportAsFixedFormat xlTypePDF, Filename:="TEST", OPENAFTERPUBLISH:=True
        
        
    MsgBox "Êã ÃäÔÇÁ ÇáÊÞÑíÑ", vbInformation
    ListBox1.Clear



    End Sub

    Tuesday, June 5, 2018 8:58 AM

Answers

  • Hello TAREK SHARAF,

    We need the full name of the pdf document to add it as attachments to a mail.

    You do not specific which folder to save your PDF, so the PDF should be saved to the default file location.

    So the code should look like this.

    ActiveSheet.Range("A:I").ExportAsFixedFormat xlTypePDF, Filename:="Test", OPENAFTERPUBLISH:=True
    pdfFolderPath = Application.DefaultFilePath
    fullFdfFileName = pdfFolderPath & "\" & "Test.pdf"
    Set olApp = CreateObject("Outlook.Application")
    Set mail = olApp.createitem(0)
    With mail
    .Attachments.Add fullFdfFileName
    .display
    End With

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by TAREK SHARAF Wednesday, June 6, 2018 12:07 PM
    Wednesday, June 6, 2018 3:19 AM

All replies

  • Hello TAREK SHARAF,

    We need the full name of the pdf document to add it as attachments to a mail.

    You do not specific which folder to save your PDF, so the PDF should be saved to the default file location.

    So the code should look like this.

    ActiveSheet.Range("A:I").ExportAsFixedFormat xlTypePDF, Filename:="Test", OPENAFTERPUBLISH:=True
    pdfFolderPath = Application.DefaultFilePath
    fullFdfFileName = pdfFolderPath & "\" & "Test.pdf"
    Set olApp = CreateObject("Outlook.Application")
    Set mail = olApp.createitem(0)
    With mail
    .Attachments.Add fullFdfFileName
    .display
    End With

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by TAREK SHARAF Wednesday, June 6, 2018 12:07 PM
    Wednesday, June 6, 2018 3:19 AM
  • dear mr / terry

    thanks too much for your help

    accept my greeting

    Wednesday, June 6, 2018 12:07 PM