0

We run an SQL query and paste the results in sheet1, then transpose the results data to sheet2, then email sheet2 data to someone.

I’ve written the vba code for the above task but im not getting these required results:

  • Line break is not need like in the below screenshot
  • Need to remove space vbtab between two sentences in email body
  • Format of the email should in plain text

Is there any possibility for running an SQL query automatically through VBA code? If yes please suggest.

Below is my code

Option Explicit

Sub sendemail_excel()

Sheet2.Cells.Clear
Worksheets("sheet1").Activate

    Range("A1").Activate  

Do Until ActiveCell.Value = ""
    Sheet1.Activate
    ActiveCell.Offset(1, 0).Select
    Range(ActiveCell, ActiveCell.End(xlToRight)).Copy
    Worksheets("Sheet2").Activate
    Range("B" & Rows.Count).End(xlUp).Select
    ActiveCell.Offset(2, 0).PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, Transpose:=True, skipblanks:=True
    'ActiveCell.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, Transpose:=True, skipblanks:=True

   'ActiveCell.Offset(1, 0).Activate
    'Range("B1").Select
    'ActiveCell.End(xlDown).Activate
    'ActiveCell.Offset(2, 0).Activate

    Range("B" & Rows.Count).End(xlUp).Offset(-2, 0).NumberFormat = "M/D/YYYY H:MM:SS AM/PM"
    Range("B" & Rows.Count).End(xlUp).Select
    ActiveCell.Offset(-4, -1).Value = "FEEDFILENAME="
    ActiveCell.Offset(-3, -1).Value = "RECIPIENT="
    ActiveCell.Offset(-2, -1).Value = "PROCESSEDTIME="
    ActiveCell.Offset(-1, -1).Value = "PROCESSSTATUS="
    ActiveCell.Offset(0, -1).Value = "NUMBEROFROWS="
     Worksheets("sheet1").Activate
    Loop

    Columns.AutoFit
    Range("B:B").HorizontalAlignment = xlLeft
    Application.ScreenUpdating = False
    Application.CutCopyMode = False
    Range("B" & Rows.Count).End(xlUp).Select
    ActiveCell.Select^
    Range(Selection, Cells(3, 1)).HorizontalAlignment = xlLeft

    Sheet2.Activate 
Range("B" & Rows.Count).End(xlUp).Select
    ActiveCell.Select

    Range(Selection, Cells(3, 1)).Select
ActiveWorkbook.EnvelopeVisible = True

    With ActiveSheet.MailEnvelope
      .Introduction = "Hi abc Team - This is the confirmation mail regarding the files, which xyz" & " " & Date - 1
      .Item.To = "chinnolamanohar@gmail.com"
      .Item.CC = "chinnolamanohar@gmail.com"
      .Item.Subject = "Confirmation for earnings feed from abc equity research to xyz"
      .Item.Send
   End With

    End Sub
Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
Chinnola Manohar
  • 1

1 Answers1

0

You can run queries directly against a database, lots of documentation online. Your connection string will vary on database type - MSSQL, MySQL etc.

Accessing SQL Database in Excel-VBA https://www.ptr.co.uk/blog/using-excel-vba-query-sql-server-database

JJWL
  • 46
  • 3